Skip to main content

Celonis Product Documentation

PU_LAST
Description

Returns the last element of the specified source column for each element of the given target 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.

Syntax
 PU_LAST ( 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:

  • 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 last element. ASC or DESC can be specified to use ascending or descending ordering. If the order direction is not specified, the ascending (ASC) order is used. Using PU_LAST with descending order is equivalent to using PU_FIRST 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:

  1. using it on a table with implicit sorting (e.g., an activity table).

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

Output

caseTable

caseId : int

companyCode : string

value : int

1

'001'

600

2

'001'

400

3

'001'

200

4

'002'

100

5

'002'

500

6

'003'

200

companyDetail

companyCode : string

country : string

'001'

'DE'

'002'

'DE'

'003'

'US'

Foreign Keys

caseTable.companyCode

companyDetail.companyCode

Result

Column1 : string

Column2 : int

'001'

1

'002'

5

'003'

6

[2]

Return the eventtime of the last activity for each case:

Query

Column1

         "companyDetail"."caseId"
        

Column2

         PU_LAST ( "companyDetail" , "activityTable"."eventtime" )
        

Input

Output

activityTable

caseId : int

activity : string

eventtime : 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 : int

country : string

1

'DE'

2

'DE'

Foreign Keys

companyDetail.caseId

activityTable.caseId

Result

Column1 : int

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

Output

activityTable

caseId : int

activity : string

eventtime : 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 : int

country : string

1

'DE'

2

'DE'

Foreign Keys

companyDetail.caseId

activityTable.caseId

Result

Column1 : int

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

Output

activityTable

caseId : int

activity : string

eventtime : 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 : int

country : string

1

'DE'

2

'DE'

Foreign Keys

companyDetail.caseId

activityTable.caseId

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

Output

activityTable

caseId : int

activity : string

eventtime : 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 : int

country : string

1

'DE'

2

'DE'

Foreign Keys

companyDetail.caseId

activityTable.caseId

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

Output

activityTable

caseId : int

activity : string

eventtime : 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 : int

country : string

1

'DE'

2

'DE'

Foreign Keys

companyDetail.caseId

activityTable.caseId

Result

Column1 : int

Column2

1

null

2

null

See also: