PU_FIRST
Description
Returns the first element of the specified source column for each element in the given target table. An order by expression can be set to define the order that should be used to determine the first element.
PU_FIRST can be applied on any data type. The data type of the result is the same as the input column data type.
Syntax
PU_FIRST ( target_table, source_table.column [, filter_expression] [, ORDER BY source_table.column [ASC|DESC] ] )
target_table: The table to which the aggregation result should be pulled. This can be:
a table from the data model. It needs to be, directly or indirectly, connected to the source_table, and there must be a 1:N relationship between the target_table and the source_table. Further documentation about join relationships can be found in Join functionality.
DOMAIN_TABLE or CONSTANT (see Pull Up Aggregation Table Options).
source_table.column: The column which should be aggregated for every row of the target_table.
filter_expression (optional): An optional filter expression to specify which values of the source_table.column should be taken into account for the aggregation.
ORDER BY (optional): Elements of the specified column are used to determine the first element.
ASCorDESCcan be specified to use ascending or descending ordering. If the order direction is not specified, the ascending (ASC) order is used. UsingPU_FIRSTwith descending order is equivalent to using PU_LAST with ascending order.
Warning
Ordering without explicit ORDER BY There is no guarantee on the order of the result returned if no explicit ORDER BY column is given. The only clearly defined cases without ORDER BY are when:
using it on a table with implicit sorting (e.g., an activity table).
using to access a column of a grouper key (e.g. if your grouping by column A and B and access the value of column A in an aggregation column) or a value depends on the grouper key.
NULL handling
If no value in the source table column exists for the element in the target table (either because all values of the source table are filtered out, or because no corresponding value exists in the first place), NULL will be returned. NULL values in the source table column are treated as if the row does not exist.
Use Cases
PU_FIRSTcan be used for Multiple Invoices per Case.
Examples
[1] Return the case ID of the smallest case table value for each company code: | |||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||
|
[2] Return the eventtime of the first activity for each case: | |||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||
|
[3] Return the eventtime of the first activity that contains a 'B' for each case: | |||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||
|
[4] PU-functions can be used in a FILTER. In this example, the cases are filtered such that the eventtime of the first activity that contains a 'B' happens after January 31st, 2016. | |||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||
|
[5] PU-functions can be used inside another aggregation function. In this example, the median of the eventtimes of the first activity for each case that contains a 'B' is calculated: | |||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||
|
[6] Return the eventtime of the first activity that contains a 'X' for each case. As there exists no such activity, all activity table values are filtered out, which means that in both cases NULL is returned. | |||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||
|