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

Description

Returns the last 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 last element.

PU_LAST can be applied on any data type. The data type of the result is the same as the input column data type.

If no value in the parent table exists for the element in the child table (either because all values of the parent table column 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.

Syntax

PU_LAST ( child_table, parent_table.column [, filter_expression] [, order_by_expression] )
PU_LAST ( DOMAIN_TABLE ( column1,...,columnN ), parent_table.column [, filter_expression] [, order_by_expression] )

Examples


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

Query
Column1
"companyDetail"."companyCode"
Column2
PU_LAST("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'
1
'002'
4
'003'
6



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

Query
Column1
"companyDetail"."caseId"
Column2
PU_LAST("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
Sat Apr 02 2016 16:00:00.000
2
Fri Jan 01 2016 17:00:00.000



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

Query
Column1
"companyDetail"."caseId"
Column2
PU_LAST("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
Sat Apr 02 2016 16: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 last activity that contains a 'B' happens after 1st of March, 2016.

Query
Filter
FILTER PU_LAST ( "companyDetail", "activityTable"."eventtime", "activityTable"."activity" LIKE 'B' ) > {d '2016-03-01'}
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 last activity for each case that contains a 'B' is calculated:

Query
Column1
MEDIAN ( PU_LAST ( "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
Sat Apr 02 2016 16:00:00.000



[6] Return the eventtime of the last 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_LAST("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



[7] For each case ID, return the last case ID of the associated company code using DOMAIN_TABLE:

Query
Column1
"caseTable"."caseId"
Column2
PU_LAST(DOMAIN_TABLE("caseTable"."companyCode"), "caseTable"."caseId", order by "caseTable"."caseId")
Input
caseTable
caseId : INTcompanyCode : STRINGvalue : INT
1
'001'
600
2
'001'
400
3
'001'
200
4
'002'
300
5
'002'
300
6
'003'
200
Output
Result
Column1 : INTColumn2 : INT
1
3
2
3
3
3
4
5
5
5
6
6


See also:

  • No labels