Skip to main content

Celonis Product Documentation

Filter propagation

Description

Description of how FILTERs are propagated to other tables in the data model.

Filters are not immediately propagated. They remain with the table where they were calculated. The filters on tables that are connected in the data model are propagated to this table, but only when it is queried. We merge all filter conditions together using a logical AND.

When a filter is propagated to another table where rows do not have a join partner, those rows are always discarded from the result.

[1]

Simple example of two filter statements on the activity table. Both filters are merged with a logical AND. As we query the activity table, no propagation to another table is happening:

Query

Filter

         FILTER "ActivityTable"."Activity" != 'A';
        

Filter

         FILTER "ActivityTable"."Activity" != 'B';
        

Column1

         "ActivityTable"."CaseID"
        

Column2

         "ActivityTable"."Activity"
        

Input

Output

ActivityTable

CaseID : int

Activity : string

Timestamp : date

1

'A'

Wed Jan 01 2020 13:00:00.000

1

'B'

Wed Jan 01 2020 14:00:00.000

2

'C'

Wed Jan 01 2020 13:00:00.000

2

'B'

Wed Jan 01 2020 14:00:00.000

2

'D'

Wed Jan 01 2020 15:00:00.000

CaseTable

CaseID : int

Customer : string

1

'Customer - X'

2

'Customer - Y'

Foreign Keys

CaseTable.CaseID

ActivityTable.CaseID

Result

Column1 : int

Column2 : string

2

'C'

2

'D'

[2]

In this example, the activity filter from the previous example is propagated to the case table, because the case table is queried. As Case 1 is eliminated by the filter on the activity table, it is also filtered out in the case table. As there are still rows left from Case 2 after applying the filter, Case 2 is contained in the result:

Query

Filter

         FILTER "ActivityTable"."Activity" != 'A';
        

Filter

         FILTER "ActivityTable"."Activity" != 'B';
        

Column1

         "CaseTable"."CaseID"
        

Column2

         "CaseTable"."Customer"
        

Input

Output

ActivityTable

CaseID : int

Activity : string

Timestamp : date

1

'A'

Wed Jan 01 2020 13:00:00.000

1

'B'

Wed Jan 01 2020 14:00:00.000

2

'C'

Wed Jan 01 2020 13:00:00.000

2

'B'

Wed Jan 01 2020 14:00:00.000

2

'D'

Wed Jan 01 2020 15:00:00.000

CaseTable

CaseID : int

Customer : string

1

'Customer - X'

2

'Customer - Y'

Foreign Keys

CaseTable.CaseID

ActivityTable.CaseID

Result

Column1 : int

Column2 : string

2

'Customer - Y'

[3]

This example shows that filters are applied after other calculations. First, the VARIANT function is evaluated, and the result is added to the case table. The filter on the activity table is then propagated to the case table, which eliminates Case 1. Case 2 with its full variant string is contained in the result:

Query

Filter

         FILTER "ActivityTable"."Activity" != 'A';
        

Filter

         FILTER "ActivityTable"."Activity" != 'B';
        

Column1

         "CaseTable"."CaseID"
        

Column2

         VARIANT ( "ActivityTable"."Activity" )
        

Input

Output

ActivityTable

CaseID : int

Activity : string

Timestamp : date

1

'A'

Wed Jan 01 2020 13:00:00.000

1

'B'

Wed Jan 01 2020 14:00:00.000

2

'C'

Wed Jan 01 2020 13:00:00.000

2

'B'

Wed Jan 01 2020 14:00:00.000

2

'D'

Wed Jan 01 2020 15:00:00.000

CaseTable

CaseID : int

Customer : string

1

'Customer - X'

2

'Customer - Y'

Foreign Keys

CaseTable.CaseID

ActivityTable.CaseID

Result

Column1 : int

Column2 : string

2

'C, B, D'

[4]

In this example, the last row of the activity table (caseID 3) does not have a join partner in the case table. We filter on case 2 in the case table and query the activity table. Case 1 is filtered out by the propagated filter, and case 3 does not have a join partner and is therefore discarded from the result as well. As a consequence, only rows of case 2 are in the result:

Query

Filter

         FILTER "CaseTable"."Customer" = 'Customer - Y';
        

Column1

         "ActivityTable"."CaseID"
        

Column2

         "ActivityTable"."Activity"
        

Input

Output

ActivityTable

CaseID : int

Activity : string

Timestamp : date

1

'A'

Wed Jan 01 2020 13:00:00.000

1

'B'

Wed Jan 01 2020 14:00:00.000

2

'C'

Wed Jan 01 2020 13:00:00.000

2

'B'

Wed Jan 01 2020 14:00:00.000

3

'A'

Wed Jan 01 2020 13:00:00.000

CaseTable

CaseID : int

Customer : string

1

'Customer - X'

2

'Customer - Y'

Foreign Keys

CaseTable.CaseID

ActivityTable.CaseID

Result

Column1 : int

Column2 : string

2

'C'

2

'B'

See also: