Pull Up aggregation
Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6
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, 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.
Syntax
PU_X ( child_table, parent_table.column [, filter_expression] )
where PU_X is one of:
PU_X ( child_table, parent_table.column [, filter_expression] [, order by parent_table.column] )
where PU_X is one of:
PU_QUANTILE ( child_table, parent_table.column, quantile [, filter_expression] )
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.
DOMAIN_TABLE
It is not possible to use the same table as the parent and the child table in a PU function. However, the DOMAIN_TABLE function can be used to create a temporary table from various column(s). These column(s) must have a common parent table to which the columns are joined to. The column for the PU function has to be from a common parent table of the specified column(s) for the temporary table.
The temporary table created by DOMAIN_TABLE contains all unique combinations of values from the specified column(s) existing in the result of joining these column(s) to their first common parent table. The resulting temporary table is a child table of the first common parent table of the specified column(s).
Syntax
PU_X ( DOMAIN_TABLE ( column1,...,columnN ), parent_table.column [, filter_expression])
where PU_X is one of:
PU_X ( DOMAIN_TABLE ( column1,...,columnN ), parent_table.column [, filter_expression] [, order by expression] )
where PU_X is one of:
PU_QUANTILE ( DOMAIN_TABLE ( column1,...,columnN ), parent_table.column, quantile [, filter_expression] )