Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

PU_FIRST

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:

  • 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

Output

caseTable

caseId : INT

companyCode : STRING

value : INT

1

'001'

600

2

'001'

400

3

'001'

200

4

'002'

300

5

'002'

300

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'

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

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

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

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

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

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 first activity for each case that contains a 'B' is calculated.

Query

Column1

MEDIAN (
    PU_FIRST ( "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

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

Ouput

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

null

2

null

See also: