Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

END_TIMESTAMP_COLUMN

Applies to: CELONIS 4.7

Description

END_TIMESTAMP_COLUMN allows to refer the end timestamp column of an activity table in the data model without using the exact column name. If no end timestamp column is defined for the target activity table, the timestamp column is referenced.

Syntax
END_TIMESTAMP_COLUMN ( [ expression ] )
  • When an expression that involves a column of an activity table is passed as an argument, the end timestamp column of that activity table is referenced.

  • When no argument is passed, the end timestamp column of the default activity table is referenced.

Examples

[1] In this example, the data model contains one activity table where an end timestamp column is specified. Using a column reference as the input expression, we refer to the END_TIMESTAMP column of the related activity table.

Query

Column1

END_TIMESTAMP_COLUMN ( "Activities"."ACTIVITY" )

Input

Output

Activities

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

END_TIMESTAMP : DATE

'1'

'A'

Wed Jan 01 2020 00:00:00.000

Wed Jan 01 2020 02:00:00.000

'1'

'B'

Thu Jan 02 2020 00:00:00.000

Thu Jan 02 2020 01:00:00.000

'2'

'A'

Wed Jan 01 2020 00:00:00.000

Wed Jan 01 2020 01:00:00.000

'2'

'C'

Thu Jan 02 2020 00:00:00.000

Thu Jan 02 2020 02:00:00.000

'3'

'D'

Wed Jan 01 2020 00:00:00.000

Wed Jan 01 2020 03:00:00.000

Result

Column1 : DATE

Wed Jan 01 2020 02:00:00.000

Thu Jan 02 2020 01:00:00.000

Wed Jan 01 2020 01:00:00.000

Thu Jan 02 2020 02:00:00.000

Wed Jan 01 2020 03:00:00.000

[2] In this example, the data model contains one activity table without an end timestamp column. In this scenario END_TIMESTAMP refers to the TIMESTAMP column of the related activity table.

Query

Column1

END_TIMESTAMP_COLUMN ( "Activities"."ACTIVITY" )

Input

Output

Activities

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Wed Jan 01 2020 00:00:00.000

'1'

'B'

Thu Jan 02 2020 00:00:00.000

'2'

'A'

Wed Jan 01 2020 00:00:00.000

'2'

'C'

Thu Jan 02 2020 00:00:00.000

'3'

'D'

Wed Jan 01 2020 00:00:00.000

Result

Column1 : DATE

Wed Jan 01 2020 00:00:00.000

Thu Jan 02 2020 00:00:00.000

Wed Jan 01 2020 00:00:00.000

Thu Jan 02 2020 00:00:00.000

Wed Jan 01 2020 00:00:00.000

[3] In this example, the data model contains one activity table where an end timestamp column is specified. By passing no arguments to the END_TIMESTAMP_COLUMN function, we refer to the END_TIMESTAMP column of the default activity table.

Query

Column1

END_TIMESTAMP_COLUMN ( )

Input

Output

Activities

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

END_TIMESTAMP : DATE

'1'

'A'

Wed Jan 01 2020 00:00:00.000

Wed Jan 01 2020 02:00:00.000

'1'

'B'

Thu Jan 02 2020 00:00:00.000

Thu Jan 02 2020 01:00:00.000

'2'

'A'

Wed Jan 01 2020 00:00:00.000

Wed Jan 01 2020 01:00:00.000

'2'

'C'

Thu Jan 02 2020 00:00:00.000

Thu Jan 02 2020 02:00:00.000

'3'

'D'

Wed Jan 01 2020 00:00:00.000

Wed Jan 01 2020 03:00:00.000

Result

Column1 : DATE

Wed Jan 01 2020 02:00:00.000

Thu Jan 02 2020 01:00:00.000

Wed Jan 01 2020 01:00:00.000

Thu Jan 02 2020 02:00:00.000

Wed Jan 01 2020 03:00:00.000