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 aFILTER_TO_NULL
statement.A FILTER statement containing
FILTER_TO_NULL
is sorted to the end, which means that it takes all otherFILTER
statements into account, independent of the given order ofFILTER
statements. Using multipleFILTER
statements containingFILTER_TO_NULL
is not supported. It is discouraged to useFILTER_TO_NULL
insideFILTER
statements. This can lead to significant performance issues.
Examples
[1] The FILTER statement is propagated to | ||||||||||||||||||||||
| ||||||||||||||||||||||
|
[2] The | ||||||||||||||
| ||||||||||||||
|
[3] The | ||||||||||||||
| ||||||||||||||
|