Skip to main content

Celonis Product Documentation

FILTER
Description

Filters can be defined as Analysis filters, Sheet filters or Component filters. If a query is sent to Celonis, all active filters are propagated to the requested table(s). Multiple filters on a table are merged together by a logical AND.

Filter Propagation

Filter propagation is necessary if there are one or more tables on which a filter is applied, which are not the same as the result table. In that case Celonis propagates the filters to the result table, along the specified join graph. For more information on the join graph see Joins).

Stable Filter

All filters in Celonis are stable. Stable in this context means that filters don't interfere with each other. As a result of this, the order of the filters doesn't matter. For example:

 FILTER table.col > 400; FILTER table.col < 600;

returns the same as

 FILTER table.col < 600; FILTER table.col > 400;

Filter stability is also the reason why Celonis doesn't support filters on aggregations, because they would not be stable. For example:

 FILTER table.col > 400; FILTER AVG(table.col) < 400;

can return a different result than

 FILTER AVG(table.col) < 400; FILTER table.col > 400;
Syntax
 FILTER [FORCED] condition;
NULL handling

If a condition evaluates to NULL, it is treated as false. Please refer to the logical operators and predicates for further information on how a condition might evaluate to NULL.

Filtering non-NULL values

To filter on all non-NULL values, either use IS NULL or the ISNULL function.

Constant Comparison

Using constant comparisons in a filter statement is only valid if the comparison is AND/OR'ed with another comparison which is bound to a table. The following are valid examples containing constant comparisons:

 FILTER 'US' = 'DE' AND "Table"."Country" IN ('DE','US');

 FILTER "Table"."Country" IN ('DE','US') OR 'US' != 'DE' AND 'US' != 'UK';
Forced Filter

If a regular filter is set as a sheet or component filter, the affected Dropdown and Button Dropdown components still show all available values. If only those values which respect the filter should be displayed and selectable, a forced filter can be used. Analysis filters are forced by default.

Example

Dropdown component using this regular sheet or component filter:

 FILTER "Table"."Country" IN ('DE','US');
filter1.png

All values are available in the dropdown menu. Values which do not match the filter condition are displayed in gray color.

Dropdown component using this forced sheet or component filter:

 FILTER FORCED "Table"."Country" IN ('DE','US');
filter2.png

Only values which match the filter condition are available.

Examples

[1]

Example where one filter is applied to the query. The filter condition excludes the second input row.

Query

Filter 

         FILTER "Numbers"."number" != 22;

Column1 

         "Numbers"."id"

Column2 

         "Numbers"."number"

Input

Output

Numbers

id : int

number : int

1

13

2

22

3

34

Result

Column1 : int

Column2 : int

1

13

3

34

[2]

Example where one filter is applied to the query. The filter condition excludes the second input row.

Query

Filter 

         FILTER "Numbers"."number" IN ( 13 , 34 );

Column1 

         "Numbers"."id"

Column2 

         "Numbers"."number"

Input

Output

Numbers

id : int

number : int

1

13

2

22

3

34

Result

Column1 : int

Column2 : int

1

13

3

34

[3]

Example where two filters are applied to the query. Both filter conditions are merged together by a logical AND. The first filter condition excludes the second input row, and the second filter condition excludes the first input row. Therefore, only the third row appears in the result.

Query

Filter 

         FILTER "Numbers"."number" IN ( 13 , 34 );

Filter 

         FILTER "Numbers"."id" IN ( 2 , 3 );

Column1 

         "Numbers"."id"

Column2 

         "Numbers"."number"

Input

Output

Numbers

id : int

number : int

1

13

2

22

3

34

Result

Column1 : int

Column2 : int

3

34

[4]

Example where one filter is applied to the query. The SUM aggregate function is applied after the filter has been applied to the input table.

Query

Filter 

         FILTER "Numbers"."number" IN ( 13 , 34 );

Column1 

         SUM ( "Numbers"."number" )

Input

Output

Numbers

id : int

number : int

1

13

2

22

3

34

Result

Column1 : int

47

[5]

Example where two filters are applied to the query. Both filter conditions are merged together by a logical AND. The first filter condition excludes the first and third input row, and the second filter condition excludes the second and third input row. Therefore, the result is empty.

Query

Filter 

         FILTER "Numbers"."number" NOT IN ( 13 , 34 );

Filter 

         FILTER "Numbers"."id" = 1;

Column1 

         "Numbers"."id"

Column2 

         "Numbers"."number"

Input

Output

Numbers

id : int

number : int

1

13

2

22

3

34

(empty table)

[6]

Example of two joined tables where one filter is applied to the query. The filter condition excludes the last row of the companyDetail input table, therefore, the last two rows of the caseTable are excluded.

Query

Filter 

         FILTER "companyDetail"."country" = 'DE';

Column1 

         "caseTable"."caseId"

Input

Output

caseTable

caseId : int

companyCode : string

value : int

1

'001'

600

2

'001'

400

3

'001'

200

4

'002'

300

5

'003'

300

6

'003'

200

companyDetail

companyCode : string

country : string

'001'

'DE'

'002'

'DE'

'003'

'US'

Foreign Keys

caseTable.companyCode

companyDetail.companyCode

Result

Column1 : int

1

2

3

4

[7]

Example of two joined tables where one filter is applied to the query. The filter condition excludes the last four rows of the caseTable.

Query

Filter 

         FILTER "caseTable"."value" > 300;

Column1 

         "caseTable"."caseId"

Column2 

         "companyDetail"."country"

Input

Output

caseTable

caseId : int

companyCode : string

value : int

1

'001'

600

2

'001'

400

3

'001'

200

4

'002'

300

5

'003'

300

6

'003'

200

companyDetail

companyCode : string

country : string

'001'

'DE'

'002'

'DE'

'003'

'US'

Foreign Keys

caseTable.companyCode

companyDetail.companyCode

Result

Column1 : int

Column2 : string

1

'DE'

2

'DE'

[8]

Example where DATE columns are being compared to DATE constants:

Query

Filter 

         FILTER "Astronomer"."Birth" < {d '1600-01-01' } AND "Astronomer"."Death" > {d '1599-12-31' };

Column1 

         "Astronomer"."Name"

Input

Output

Astronomer

Name : string

Birth : date

Death : date

'Tycho Brahe'

Sat Dec 04 1546 00:00:00.000

Wed Oct 24 1601 00:00:00.000

'Giovanni Domenico Cassini'

Sun Jun 08 1625 00:00:00.000

Wed Sep 14 1712 00:00:00.000

'Galileo Galilei'

Sat Feb 05 1564 00:00:00.000

Wed Jan 08 1642 00:00:00.000

'Christiaan Huygens'

Sat Apr 14 1629 00:00:00.000

Fri Jul 08 1695 00:00:00.000

'Johannes Kepler'

Mon Dec 17 1571 00:00:00.000

Fri Nov 15 1630 00:00:00.000

Result

Column1 : string

'Tycho Brahe'

'Galileo Galilei'

'Johannes Kepler'

[9]

Example where we filter for DATE values from the last two weeks - and implicitly including possible timestamps in the future - by using a combination of ADD_DAYS and TODAY:

Query

Filter 

         FILTER "Table1"."Date" > ADD_DAYS ( TODAY ( ) , - 14 );

Column1 

         "Table1"."Value"

Input

Output

Table1

Date : date

Value : string

Fri Jan 19 2024 12:03:31.329

'20 days ago'

Wed Jan 24 2024 12:03:31.329

'15 days ago'

Mon Jan 29 2024 12:03:31.329

'10 days ago'

Sat Feb 03 2024 12:03:31.329

'5 days ago'

Thu Feb 08 2024 12:03:31.329

'Today'

Result

Column1 : string

'10 days ago'

'5 days ago'

'Today'

[10]

Applying a filter which compares a column against null always returns an empty result.

Query

Filter 

         FILTER Table1.Column1 = NULL;

Column1 

         Table1.Column1

Input

Output

Table1

Column1 : int

1

null

(empty table)

Warning

EQUALS: Comparison with NULL always returns NULL. To check for NULL values, please use <value> IS NULL or ISNULL(<value>)=1.

[11]

Applying a filter which compares a column against not null always returns an empty result.

Query

Filter 

         FILTER Table1.Column1 != NULL;

Column1 

         Table1.Column1

Input

Output

Table1

Column1 : int

1

null

(empty table)

Warning

NOT EQUALS: Comparison with NULL always returns NULL. To check for NULL values, please use <value> IS NULL or ISNULL(<value>)=1.

See also: