Skip to main content

Celonis Product Documentation

FILTER_TO_NULL
Description

FILTER_TO_NULL immediately propagates the FILTER to the specified column by setting all violating rows to NULL.

FILTER_TO_NULL takes the current filter state into account and recalculates the result every time a filter changes. If a filter is applied on a table, which is not the same as for the specified column within the FILTER_TO_NULL operator, Celonis propagates the filter to the result table, along the specified join graph. For more information on the join graph see Joins.

FILTER_TO_NULL can be used to make certain functions filter-aware, like PU-functions, window functions, or process functions.

Syntax
  FILTER_TO_NULL ( table.column )
 
Usage inside FILTER statements

It is discouraged to use FILTER_TO_NULL inside FILTER statements. This can lead to significant performance issues.

A FILTER statement containing FILTER_TO_NULL is sorted to the end, which means that it takes all other FILTER statements into account, independent of the given order of FILTER statements. Using multiple FILTER statements containing FILTER_TO_NULL is not supported.

Examples

[1]

The FILTER statement is propagated to Table1.value via the FILTER_TO_NULL operator before the PU_AVG function is executed.

Query

Filter

         FILTER "Table1"."field" = 'X';
        

Column1

         PU_AVG ( DOMAIN_TABLE ( "Table1"."category" ) , FILTER_TO_NULL ( "Table1"."value" ) )
        

Input

Output

Table1

category : string

field : string

value : int

'A'

'X'

1

'A'

'Y'

10

'B'

'X'

100

'B'

'Y'

1000

Result

Column1 : float

1.0

100.0

[2]

The FILTER statement is propagated to Table1.column via the FILTER_TO_NULL operator before the LAG function is executed.

Query

Filter

         FILTER "Table1"."column" IN ( 'A' , 'B' , 'E' );
        

Column1

         LAG ( FILTER_TO_NULL ( "Table1"."column" ) )
        

Input

Output

Table1

column : string

'A'

'B'

'C'

'D'

'E'

Result

Column1

null

A

B

[3]

The FILTER statement is propagated to Table1.column via the FILTER_TO_NULL operator before the INDEX_ORDER function is executed.

Query

Filter

         FILTER "Table1"."column" > 12;
        

Column1

         INDEX_ORDER ( FILTER_TO_NULL ( "Table1"."column" ) )
        

Input

Output

Table1

column : int

11

13

14

12

15

Result

Column1 : int

1

2

3