Description
The Pull-Up-Functions allow you to aggregate a column based on another table. You can define the child table to which the parent tables entries are pulled, and you can explicitly define on which basis calculations are executed.
Pull-Up-Functions are used to achieve nested aggregations and filters on aggregations. Both can not be done by standard aggregations. Celonis deliberately does not support nested standard aggregations in order to avoid subqueries which would significantly increase the language complexity. Applying filters on standard aggregations is not supported because the resulting filters would not be stable, as explained in filters.
The Pull-Up-Functions support nested aggregations and filters on aggregations by applying filters differently than the standard aggregations. Standard aggregations take the current filter state into account and recalculate their result every time a filter changes. In contrast to that the Pull-Up-Functions ignore the global filter state and are calculated only once. Actually Pull-Up-Functions dynamically extend a data model. Therefore the result of a Pull-Up-Function can be used like a column of a table. For example it can be used as an input for another aggregation or as basis for a filter.
Filter expressions can be defined to specify which values should be taken into account for the aggregation.
A 1:N relationship between the child and parent table is required.
For a 1:N:1 relationship, the BIND operator can be used in PU functions. It binds the values of the right table to the middle table, hence these values can be used then. An example can be found in the BIND documentation.
Syntax
PU_X ( child_table, parent_table.column [, filter_expression] )
PU_X ( child_table, parent_table.column [, filter_expression] [, order by parent_table.column] )
PU_QUANTILE ( child_table, parent_table.column, quantile [, filter_expression] )
PU_STRING_AGG ( child_table, parent_table.column, delimiter [, filter_expression] [, order by parent_table.column [ASC|DESC] ] )
The following arguments can be passed to all PU functions:
- child_table: The table to which the aggregation result should be pulled. This can be:
- a table from the data model. This table needs to be a child table of the parent_table. Documentation about parent and child tables can be found in Join functionality.
- DOMAIN_TABLE or CONSTANT (see Pull Up Aggregation Table Options).
- parent_table.column: The column which should be aggregated for every row of the child_table.
- filter_expression (optional): An optional filter expression to specify which values of the parent_table.column should be taken into account for the aggregation.
Filter behavior
The way PU functions handle filters is different compared to the standard aggregation. In contrast to the standard aggregation, PU functions ignore filters, meaning that if a filter or a selection is changed, the result of the PU function is not recalculated. Another difference to the standard aggregation is that it is possible to filter on the result of a PU function.
NULL handling
If no value in the parent table exists for the element in the child table (either because all values of the parent table are filtered out, or because no corresponding value exists in the first place), 0 (PU_COUNT, PU_COUNT_DISTINCT) or NULL (all other PU functions) will be returned. NULL values in the parent table column are treated as if the row does not exist.