Applies to:  CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7 

Description

Returns the first element of the specified column for each element of the given child 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 ( child_table, parent_table.column [, filter_expression] [, ORDER BY parent_table.column] )
  • 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.
    • CONSTANT.
  • 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.
  • ORDER BY (optional): Elements of the specified column in ascending order are used to determine the first element. For the first element in descending order, PU_LAST can be used.

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), NULL will be returned. NULL values in the parent table column are treated as if the row does not exist.

Examples


[1] Return the case ID of the smallest case table value for each company code:

Query
Column1
"companyDetail"."companyCode"
Column2
PU_FIRST ( "companyDetail" , "caseTable"."caseId" , order by "caseTable"."value" )
Input
caseTable
caseId : INTcompanyCode : STRINGvalue : INT
1
'001'
600
2
'001'
400
3
'001'
200
4
'002'
300
5
'002'
300
6
'003'
200
companyDetail
companyCode : STRINGcountry : STRING
'001''DE'
'002''DE'
'003''US'

Foreign Keys
caseTable.companyCodecompanyDetail.companyCode
Output
Result
Column1 : STRINGColumn2 : INT
'001'
3
'002'
4
'003'
6



[2] Return the eventtime of the first activity for each case:

Query
Column1
"companyDetail"."caseId"
Column2
PU_FIRST ( "companyDetail" , "activityTable"."eventtime" )
Input
activityTable
caseId : INTactivity : STRINGeventtime : DATE
1
'A'
Fri Jan 01 2016 00:00:00.000
1
'B'
Tue Feb 02 2016 14:00:00.000
1
'C'
Sat Apr 02 2016 15:00:00.000
1
'B'
Sat Apr 02 2016 16:00:00.000
2
'A'
Fri Jan 01 2016 12:30:00.000
2
'B'
Fri Jan 01 2016 13:00:00.000
2
'C'
Fri Jan 01 2016 15:00:00.000
2
'D'
Fri Jan 01 2016 17:00:00.000
companyDetail
caseId : INTcountry : STRING
1
'DE'
2
'DE'

Foreign Keys
companyDetail.caseIdactivityTable.caseId
Output
Result
Column1 : INTColumn2 : DATE
1
Fri Jan 01 2016 00:00:00.000
2
Fri Jan 01 2016 12:30:00.000



[3] Return the eventtime of the first activity that contains a 'B' for each case:

Query
Column1
"companyDetail"."caseId"
Column2
PU_FIRST ( "companyDetail" , "activityTable"."eventtime" , "activityTable"."activity" LIKE 'B' )
Input
activityTable
caseId : INTactivity : STRINGeventtime : DATE
1
'A'
Fri Jan 01 2016 00:00:00.000
1
'B'
Tue Feb 02 2016 14:00:00.000
1
'C'
Sat Apr 02 2016 15:00:00.000
1
'B'
Sat Apr 02 2016 16:00:00.000
2
'A'
Fri Jan 01 2016 12:30:00.000
2
'B'
Fri Jan 01 2016 13:00:00.000
2
'C'
Fri Jan 01 2016 15:00:00.000
2
'D'
Fri Jan 01 2016 17:00:00.000
companyDetail
caseId : INTcountry : STRING
1
'DE'
2
'DE'

Foreign Keys
companyDetail.caseIdactivityTable.caseId
Output
Result
Column1 : INTColumn2 : DATE
1
Tue Feb 02 2016 14:00:00.000
2
Fri Jan 01 2016 13:00:00.000



[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.

Query
Filter
FILTER
    PU_FIRST ( "companyDetail" , "activityTable"."eventtime" , "activityTable"."activity" LIKE 'B' )
    >
    {d '2016-01-31' };
Column1
"companyDetail"."caseId"
Input
activityTable
caseId : INTactivity : STRINGeventtime : DATE
1
'A'
Fri Jan 01 2016 00:00:00.000
1
'B'
Tue Feb 02 2016 14:00:00.000
1
'C'
Sat Apr 02 2016 15:00:00.000
1
'B'
Sat Apr 02 2016 16:00:00.000
2
'A'
Fri Jan 01 2016 12:30:00.000
2
'B'
Fri Jan 01 2016 13:00:00.000
2
'C'
Fri Jan 01 2016 15:00:00.000
2
'D'
Fri Jan 01 2016 17:00:00.000
companyDetail
caseId : INTcountry : STRING
1
'DE'
2
'DE'

Foreign Keys
companyDetail.caseIdactivityTable.caseId
Output
Result
Column1 : INT
1



[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:

Query
Column1
MEDIAN (
    PU_FIRST ( "companyDetail" , "activityTable"."eventtime" , "activityTable"."activity" LIKE 'B' )
)
Input
activityTable
caseId : INTactivity : STRINGeventtime : DATE
1
'A'
Fri Jan 01 2016 00:00:00.000
1
'B'
Tue Feb 02 2016 14:00:00.000
1
'C'
Sat Apr 02 2016 15:00:00.000
1
'B'
Sat Apr 02 2016 16:00:00.000
2
'A'
Fri Jan 01 2016 12:30:00.000
2
'B'
Fri Jan 01 2016 13:00:00.000
2
'C'
Fri Jan 01 2016 15:00:00.000
2
'D'
Fri Jan 01 2016 17:00:00.000
companyDetail
caseId : INTcountry : STRING
1
'DE'
2
'DE'

Foreign Keys
companyDetail.caseIdactivityTable.caseId
Output
Result
Column1 : DATE
Tue Feb 02 2016 14:00:00.000



[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.

Query
Column1
"companyDetail"."caseId"
Column2
PU_FIRST ( "companyDetail" , "activityTable"."eventtime" , "activityTable"."activity" LIKE 'X' )
Input
activityTable
caseId : INTactivity : STRINGeventtime : DATE
1
'A'
Fri Jan 01 2016 00:00:00.000
1
'B'
Tue Feb 02 2016 14:00:00.000
1
'C'
Sat Apr 02 2016 15:00:00.000
1
'B'
Sat Apr 02 2016 16:00:00.000
2
'A'
Fri Jan 01 2016 12:30:00.000
2
'B'
Fri Jan 01 2016 13:00:00.000
2
'C'
Fri Jan 01 2016 15:00:00.000
2
'D'
Fri Jan 01 2016 17:00:00.000
companyDetail
caseId : INTcountry : STRING
1
'DE'
2
'DE'

Foreign Keys
companyDetail.caseIdactivityTable.caseId
Output
Result
Column1 : INTColumn2 : DATE
1
null
2
null


See also:

  • No labels