Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

BIND_FILTERS

Applies to: CELONIS 4.7

Description

BIND_FILTERS pulls a filter to a specified table. Multiple filters on a table are merged together by a logical AND.

BIND_FILTERS can be used in the following contexts:

Syntax
BIND_FILTERS( target_table, condition [, condition ]*)
NULL handling

If a condition returns NULL, then BIND_FILTERS returns FALSE.

Examples

[1] Binds the filters on the Activity and OrderPos tables to the case table. The actual join between those tables is not introduced, which is why the number of rows in the result equals the number of rows of the case table.

Query

Column1

CASE
    WHEN
        BIND_FILTERS (
            "caseTable" ,
            "activityTable"."activity" = 'C' ,
            "OrderPos"."city" = 'Seattle'
        )
    THEN
        'contains C, to Seattle'
    ELSE
        NULL
END

Input

Output

OrderPos

orderId : STRING

pos : INT

city : STRING

city : STRING

'001'

1

'DE'

'Frankfurt'

'001'

2

'US'

'Boston'

'001'

3

'FR'

'Lyon'

'002'

1

'US'

'Seattle'

'003'

1

'DE'

'Frankfurt'

activityTable

activity : STRING

caseId : INT

eventtime : DATE

'A'

1

Sun May 12 2019 05:48:45.000

'B'

1

Sun May 12 2019 07:12:23.000

'C'

2

Tue May 12 2020 10:43:12.000

'C'

3

Tue May 12 2020 14:18:31.000

caseTable

caseId : INT

orderId : STRING

1

'001'

2

'002'

3

'003'

Foreign Keys

caseTable.caseId

activityTable.caseId

caseTable.orderId

OrderPos.orderId

Result

Column1 : STRING

null

'contains C, to Seattle'

null

[2] A case is matched to the string value when for this case the orderId is "001" or BIND_FILTERS returns true. BIND_FILTERS is true when it has an activity "C" and an city entry with "Seattle" for the orderId.

Query

Column1

CASE
    WHEN
        BIND_FILTERS (
            "caseTable" ,
            "activityTable"."activity" = 'C' ,
            "OrderPos"."city" = 'Seattle'
        )
        OR
        "caseTable"."orderId" LIKE '001'
    THEN
        'contains C, to Seattle, or orderId is 001'
    ELSE
        NULL
END

Input

Output

orderPOS

orderId : STRING

pos : INT

country : STRING

city : STRING

'001'

1

'DE'

'Frankfurt'

'001'

2

'US'

'Boston'

'001'

3

'FR'

'Lyon'

'002'

1

'US'

'Seattle'

'003'

1

'DE'

'Frankfurt'

activityTable

activity : STRING

caseId : INT

eventtime : DATE

'A'

1

Sun May 12 2019 05:48:45.000

'B'

1

Sun May 12 2019 07:12:23.000

'C'

2

Tue May 12 2020 10:43:12.000

'C'

3

Tue May 12 2020 14:18:31.000

caseTable

caseId : INT

orderId : STRING

1

'001'

2

'002'

3

'003'

Foreign Keys

caseTable.caseId

activityTable.caseId

caseTable.orderId

OrderPos.orderId

Result

Column1 : STRING

'contains C, to Seattle, or orderId is 001'

'contains C, to Seattle, or orderId is 001'

null

[3] BIND_FILTERS filters for the activities "A" and "C" as well as for the countries "DE", "FR", "IT" as well as "ESP". In order to not being filtered out by FILTER, a case must have one of the specified activities as well as one of the countries.

Query

Filter

FILTER
    BIND_FILTERS (
        "caseTable" ,
        "activityTable"."activity" IN ( 'A' , 'C' ) ,
        "OrderPos"."country" IN ( 'DE' , 'FR' , 'IT' , 'ESP' )
    );

Column1

"caseTable"."caseId"

Input

Output

orderPos

orderId : STRING

pos : INT

country : STRING

city : STRING

'001'

1

'DE'

'Frankfurt'

'001'

2

'US'

'Boston'

'001'

3

'FR'

'Lyon'

'002'

1

'US'

'Seattle'

'003'

1

'DE'

'Frankfurt'

activityTable

activity : STRING

caseId : INT

eventtime : DATE

'A'

1

Sun May 12 2019 05:48:45.000

'B'

1

Sun May 12 2019 07:12:23.000

'C'

2

Tue May 12 2020 10:43:12.000

'C'

3

Tue May 12 2020 14:18:31.000

caseTable

caseId : INT

orderId : STRING

1

'001'

2

'002'

3

'003'

Foreign Keys

caseTable.caseId

activityTable.caseId

caseTable.orderId

OrderPos.orderId

Result

Column1 : INT

1

3

[4] BIND_FILTERS filters for activities "A" and "B". Since the only case, which has activities with "A" or "B", is case 1, the distinct activities will only be counted for this case.

Query

Column1

PU_COUNT_DISTINCT (
    DOMAIN_TABLE ( "activityTable"."caseId" ) ,
    "activityTable"."activity" ,
    BIND_FILTERS ( "caseTable" , "activityTable"."activity" IN_LIKE ( 'A' , 'B' ) )
)

Input

Output

OrderPos

orderId : STRING

pos : INT

country : STRING

city : STRING

'001'

1

'DE'

'Frankfurt'

'001'

2

'US'

'Boston'

'001'

3

'FR'

'Lyon'

'002'

1

'US'

'Seattle'

'003'

1

'DE'

'Frankfurt'

activityTable

activity : STRING

caseId : INT

eventtime : DATE

'A'

1

Sun May 12 2019 05:48:45.000

'B'

1

Sun May 12 2019 07:12:23.000

'C'

2

Tue May 12 2020 10:43:12.000

'C'

3

Tue May 12 2020 14:18:31.000

caseTable

caseId : INT

orderId : STRING

1

'001'

2

'002'

3

'003'

Foreign Keys

caseTable.caseId

activityTable.caseId

caseTable.orderId

OrderPos.orderId

Result

Column1 : INT

2

0

0

[5] CALC_REWORK only counts the activities for the case with id 1 because BIND_FILTERS filters for cases, which have an orderId having a country entry with "DE" or "FR" and a city entry with "Frankfurt" or "Lyon", and activities "A" or "C".

Query

Column1

CALC_REWORK (
    BIND_FILTERS (
        "caseTable" ,
        "OrderPos"."country" IN ( 'DE' , 'FR' ) ,
        "activityTable"."activity" IN ( 'A' , 'C' ) ,
        "OrderPos"."city" IN_LIKE ( 'Frankfurt' , 'Lyon' )
    )
)

Input

Output

OrderPos

orderId : STRING

pos : INT

country : STRING

city : STRING

'001'

1

'DE'

'Frankfurt'

'001'

2

'US'

'Boston'

'001'

3

'FR'

'Lyon'

'002'

1

'US'

'Seattle'

'003'

1

'DE'

'Frankfurt'

activityTable

activity : STRING

caseId : INT

eventtime : DATE

'A'

1

Sun May 12 2019 05:48:45.000

'B'

1

Sun May 12 2019 07:12:23.000

'C'

2

Tue May 12 2020 10:43:12.000

'C'

3

Tue May 12 2020 14:18:31.000

caseTable

caseId : INT

orderId : STRING

1

'001'

2

'002'

3

'003'

Foreign Keys

caseTable.caseId

activityTable.caseId

caseTable.orderId

OrderPos.orderId

Result

Column1 : INT

2

0

1

[6] In the first FILTER statement, only entries with the country being "DE" or "FR" are kept. Then, in the next FILTER statement only the caseTable entries with an order ID still being in OrderPos are kept. Afterwards, the filter is pulled to the activityTable filtering out all entries with case ID 2.

Query

Filter

FILTER "OrderPos"."country" IN ( 'DE' , 'FR' );

Filter

FILTER BIND_FILTERS ( "activityTable" , "caseTable"."orderId" IN ( "OrderPos"."orderId" ) );

Column1

"activityTable"."caseId"

Column2

"activityTable"."activity"

Input

Output

OrderPos

orderId : STRING

pos : INT

country : STRING

city : STRING

'001'

1

'DE'

'Frankfurt'

'001'

2

'US'

'Boston'

'001'

3

'FR'

'Lyon'

'002'

1

'US'

'Seattle'

'003'

1

'DE'

'Frankfurt'

activityTable

activity : STRING

caseId : INT

eventtime : DATE

'A'

1

Sun May 12 2019 05:48:45.000

'B'

1

Sun May 12 2019 07:12:23.000

'C'

2

Tue May 12 2020 10:43:12.000

'C'

3

Tue May 12 2020 14:18:31.000

caseTable

caseId : INT

orderId : STRING

1

'001'

2

'002'

3

'003'

Foreign Keys

caseTable.caseId

activityTable.caseId

caseTable.orderId

OrderPos.orderId

Result

Column1 : INT

Column2 : STRING

1

'A'

1

'B'

3

'C'

[7] BIND_FILTERS filters for the order IDs "001", "002", and "007". The result is then pulled to the activityTable. Since order ID "003" was not in the list of the IN-operator, case 3 was filtered out. Therefore, when pulling the filter to the activityTable, all entries with case 3 are ignored.

Query

Column1

PU_COUNT_DISTINCT (
    "caseTable" ,
    "activityTable"."activity" ,
    BIND_FILTERS ( "activityTable" , "caseTable"."orderId" IN ( '001' , '002' , '007' ) )
)

Input

Output

OrderPos

orderId : STRING

pos : INT

country : STRING

city : STRING

'001'

1

'DE'

'Frankfurt'

'001'

2

'US'

'Boston'

'001'

3

'FR'

'Lyon'

'002'

1

'US'

'Seattle'

'003'

1

'DE'

'Frankfurt'

activityTable

activity : STRING

caseId : INT

eventtime : DATE

'A'

1

Sun May 12 2019 05:48:45.000

'B'

1

Sun May 12 2019 07:12:23.000

'C'

2

Tue May 12 2020 10:43:12.000

'C'

3

Tue May 12 2020 14:18:31.000

caseTable

caseId : INT

orderId : STRING

1

'001'

2

'002'

3

'003'

Foreign Keys

caseTable.caseId

activityTable.caseId

caseTable.orderId

OrderPos.orderId

Result

Column1 : INT

2

1

0

See also: