Skip to main content

FILTER_TO_NULL

Description

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

Warning

Operator Performance As the result of FILTER_TO_NULL has to be recalculated every time the filter changes, using FILTER_TO_NULL excessively can have a negative impact on query performance. We therefore recommend to only use the function where necessary.

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 )

Specifics when using FILTER_TO_NULL

FILTER_TO_NULL should not be used in every calculation by default. For example, instead of using FILTER_TO_NULL inside a PU-functions, it is recommended to use the filter condition argument of the PU-function if possible. Instead, we recommend to only use the function where necessary. Using FILTER_TO_NULL has the following effects:

  • Using FILTER_TO_NULL can have a negative performance impact.

  • It is not possible to do a selection on result values from a statement that contains a FILTER_TO_NULL. This means that if it should be possible to, for example, filter on the result of a PU function, this PU-function cannot contain a FILTER_TO_NULL statement.

  • 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. It is discouraged to use FILTER_TO_NULL inside FILTER statements. This can lead to significant performance issues.

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