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
OrderPos
activityTable
caseTable
Foreign Keys
caseTable.caseId | activityTable.caseId |
caseTable.orderId | OrderPos.orderId |
Output
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
OrderPos
activityTable
caseTable
Foreign Keys
caseTable.caseId | activityTable.caseId |
caseTable.orderId | OrderPos.orderId |
Output
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
OrderPos
activityTable
caseTable
Foreign Keys
caseTable.caseId | activityTable.caseId |
caseTable.orderId | OrderPos.orderId |
Output
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
OrderPos
activityTable
caseTable
Foreign Keys
caseTable.caseId | activityTable.caseId |
caseTable.orderId | OrderPos.orderId |
Output
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
OrderPos
activityTable
caseTable
Foreign Keys
caseTable.caseId | activityTable.caseId |
caseTable.orderId | OrderPos.orderId |
Output
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
OrderPos
activityTable
caseTable
Foreign Keys
caseTable.caseId | activityTable.caseId |
caseTable.orderId | OrderPos.orderId |
[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
OrderPos
activityTable
caseTable
Foreign Keys
caseTable.caseId | activityTable.caseId |
caseTable.orderId | OrderPos.orderId |
Output
Result
Column1 : INT |
---|
2 |
1 |
0 |