Skip to main content

Celonis Product Documentation

BIND_FILTERS
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 ] *)
Join Behavior For Multiple Filters

Filters separated by a comma do not need to have a valid join relation between each other. But they should have a valid join relation with the target_table, otherwise they are simply ignored and have no effect on the result.

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

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

null

'contains C, to Seattle'

null

[2]

This query results in a join error since the Activity and OrderPos table do not have a valid join path. In order to make this query work, the AND can be replaced with comma separation. Then, the two filters are independently pulled to the caseTable (see the example above).

Query

Column1 

         CASE WHEN BIND_FILTERS ( "caseTable" , "activityTable"."activity" = 'C' AND "OrderPos"."city" = 'Seattle' ) THEN 'contains C, to Seattle' 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

Warning

No common table could be found. The tables ["activityTable"] and ["OrderPos"] are connected, but have no common table. This means that they do not have a direct (or indirect) 1:N or N:1 relationship. Join path: [activityTable]N <-- 1![caseTable]!1 --> N[OrderPos]. For more information on the join path, search for "Join functionality" in PQL documentation.

[3]

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

[4]

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

[5]

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

[6]

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

[7]

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'

[8]

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: