Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

DATE_MATCH

Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7

Description

DATE_MATCH returns 1 if the input date is part of each non empty filter list. Each filter list represents a date type. The first list represents a set of years, which is filtered for. The second a list of quarters and so on. An empty list is not considered for filtering.

Syntax
DATE_MATCH(column, [YEARS], [QUARTERS], [MONTHS], [WEEKS], [DAYS])
NULL handling

If the input value is NULL, then the result is NULL as well.

Examples

[1] Match date with specifying all filter lists.

Query

Column1

DATE_MATCH (
    "Table1"."Column1" ,
    [ 2008 ] ,
    [ 1 , 2 ] ,
    [ 1 , 2 , 3 , 5 ] ,
    [ 1 , 6 , 11 , 21 ] ,
    [ 1 , 8 , 15 , 22 ]
)

Input

Output

Table1

Column1 : DATE

Tue Jan 01 2008 00:00:00.000

Fri Feb 08 2008 00:00:00.000

Sat Mar 15 2008 00:00:00.000

Thu May 22 2008 00:00:00.000

Result

Column1 : INT

1

1

1

1

[2] Filter lists don't match any date.

Query

Column1

DATE_MATCH (
    "Table1"."Column1" ,
    [ 2008 ] ,
    [ 1 , 2 ] ,
    [ 1 , 2 , 3 , 5 ] ,
    [ 1 , 6 , 11 , 21 ] ,
    [ 1 , 8 , 15 , 22 ]
)

Input

Output

Table1

Column1 : DATE

Mon Jan 01 2007 00:00:00.000

Sat Mar 08 2008 00:00:00.000

Sun Mar 16 2008 00:00:00.000

Mon Jun 22 2009 00:00:00.000

Result

Column1 : INT

0

0

0

0

[3] Match date with empty filter lists for quarters and weeks.

Query

Column1

DATE_MATCH ( "Table1"."Column1" , [ 2008 ] , [ ] , [ 1 , 2 , 3 , 5 ] , [ ] , [ 1 , 8 , 15 , 22 ] )

Input

Output

Table1

Column1 : DATE

Tue Jan 01 2008 00:00:00.000

Fri Feb 08 2008 00:00:00.000

Sat Mar 15 2008 00:00:00.000

Thu May 22 2008 00:00:00.000

Result

Column1 : INT

1

1

1

1

[4] Non-empty filter lists don't match any date.

Query

Column1

DATE_MATCH ( "Table1"."Column1" , [ 2008 ] , [ ] , [ ] , [ ] , [ 1 , 2 , 3 , 4 ] )

Input

Output

Table1

Column1 : DATE

Mon Jan 01 2007 00:00:00.000

Tue Feb 05 2008 00:00:00.000

Mon Mar 10 2008 00:00:00.000

Fri May 15 2009 00:00:00.000

Result

Column1 : INT

0

0

0

0