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
orderId : STRINGpos : INTcountry : STRINGcity : STRING
'001'
1
'DE''Frankfurt'
'001'
2
'US''Boston'
'001'
3
'FR''Lyon'
'002'
1
'US''Seattle'
'003'
1
'DE''Frankfurt'
activityTable
activity : STRINGcaseId : INTeventtime : 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 : INTorderId : STRING
1
'001'
2
'002'
3
'003'

Foreign Keys
caseTable.caseIdactivityTable.caseId
caseTable.orderIdOrderPos.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
orderId : STRINGpos : INTcountry : STRINGcity : STRING
'001'
1
'DE''Frankfurt'
'001'
2
'US''Boston'
'001'
3
'FR''Lyon'
'002'
1
'US''Seattle'
'003'
1
'DE''Frankfurt'
activityTable
activity : STRINGcaseId : INTeventtime : 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 : INTorderId : STRING
1
'001'
2
'002'
3
'003'

Foreign Keys
caseTable.caseIdactivityTable.caseId
caseTable.orderIdOrderPos.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
orderId : STRINGpos : INTcountry : STRINGcity : STRING
'001'
1
'DE''Frankfurt'
'001'
2
'US''Boston'
'001'
3
'FR''Lyon'
'002'
1
'US''Seattle'
'003'
1
'DE''Frankfurt'
activityTable
activity : STRINGcaseId : INTeventtime : 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 : INTorderId : STRING
1
'001'
2
'002'
3
'003'

Foreign Keys
caseTable.caseIdactivityTable.caseId
caseTable.orderIdOrderPos.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
orderId : STRINGpos : INTcountry : STRINGcity : STRING
'001'
1
'DE''Frankfurt'
'001'
2
'US''Boston'
'001'
3
'FR''Lyon'
'002'
1
'US''Seattle'
'003'
1
'DE''Frankfurt'
activityTable
activity : STRINGcaseId : INTeventtime : 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 : INTorderId : STRING
1
'001'
2
'002'
3
'003'

Foreign Keys
caseTable.caseIdactivityTable.caseId
caseTable.orderIdOrderPos.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
orderId : STRINGpos : INTcountry : STRINGcity : STRING
'001'
1
'DE''Frankfurt'
'001'
2
'US''Boston'
'001'
3
'FR''Lyon'
'002'
1
'US''Seattle'
'003'
1
'DE''Frankfurt'
activityTable
activity : STRINGcaseId : INTeventtime : 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 : INTorderId : STRING
1
'001'
2
'002'
3
'003'

Foreign Keys
caseTable.caseIdactivityTable.caseId
caseTable.orderIdOrderPos.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
orderId : STRINGpos : INTcountry : STRINGcity : STRING
'001'
1
'DE''Frankfurt'
'001'
2
'US''Boston'
'001'
3
'FR''Lyon'
'002'
1
'US''Seattle'
'003'
1
'DE''Frankfurt'
activityTable
activity : STRINGcaseId : INTeventtime : 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 : INTorderId : STRING
1
'001'
2
'002'
3
'003'

Foreign Keys
caseTable.caseIdactivityTable.caseId
caseTable.orderIdOrderPos.orderId
Output
Result
Column1 : INTColumn2 : 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
OrderPos
orderId : STRINGpos : INTcountry : STRINGcity : STRING
'001'
1
'DE''Frankfurt'
'001'
2
'US''Boston'
'001'
3
'FR''Lyon'
'002'
1
'US''Seattle'
'003'
1
'DE''Frankfurt'
activityTable
activity : STRINGcaseId : INTeventtime : 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 : INTorderId : STRING
1
'001'
2
'002'
3
'003'

Foreign Keys
caseTable.caseIdactivityTable.caseId
caseTable.orderIdOrderPos.orderId
Output
Result
Column1 : INT
2
1
0


See also:

  • No labels