Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

MERGE_EVENTLOG

Applies to: CELONIS 4.7

Description

MERGE_EVENTLOG merges two columns of the same type from two activity tables into one common column. The sorting of entries is based on timestamps.

Given two columns of the same type from two activity tables whose case tables are connected directly or indirectly, the MERGE_EVENTLOG operator computes one common (internal) table, containing the merged result of the two input columns. In general, there are two scenarios how the activity tables can be linked in the data model. The following section describes these two scenarios. Thereby, the direction of the merge is defined by the order of the input columns. The content of the second column is always merged into the corresponding cases of the first input column. The resulting (internal) table is joined to the case table of the first input column.

1:n Scenario

Given two activity tables whose case tables are connected directly or indirectly via a 1:n relationship, MERGE_EVENTLOG computes one combined (internal) table ('Merge Table') sorted by timestamp. The resulting table is always joined to the case table of the first input column. If the first input column is e.g. from 'Activity Table 1', the resulting 'Merge Table' is joined to 'Case Table 1'.

34440393.png

The two activity tables can also share one case table like shown in the following picture.

34440394.png
n:m Scenario

In the following example, two activity tables 'Activity Table Left' and 'Activity Table Right' and their corresponding case tables 'Case Table Left' and 'Case Table Right' are given. The case tables have a n:m relationship, which is modeled via two 1:n relationships and an intermediate table 'Fact Table Mid'. MERGE_EVENTLOG computes one combined (internal) table that is joined to the case table of the first input column. If the first input column is e.g. from 'Activity Table Left', the resulting (internal) 'Merge Table' is joined to 'Case Table Left'.

34440395.png
Syntax
MERGE_EVENTLOG ( table1.column1 , table2.column1 )

  • table1.column1: Column from an activity table.

  • table2.column1: Column from an activity table.

NULL handling

  • If there is no corresponding entry for a case in the case table, this case will be ignored.

  • If in the mapping of the cases the source case id is NULL, the target case will still be in the result table.

  • If the value of the case column of a row is NULL, the row will be ignored.

  • If the value of the timestamp column is NULL, the row will be ignored.

  • If the value of an activity column is NULL, the row will be ignored.

Peculiarities

If there is a case entry in the case table but no corresponding activity with this case id in the target table (1 - side), then a case id for the result of the merge will be generated. If there is a case entry in the case table but no corresponding activity with this case id in the target table (n - side), then the joined items won't be merged.

Examples

[1] This example scenario contains two activity tables ACTIVITIES_BSEG and ACTIVITIES_BKPF joined to their corresponding case tables BSEG and BKPF. The relationship between BSEG and BKPF is n:1. In the example query, the activity column from table ACTIVITIES_BSEG is the first input parameter. Therefore, the activities from table ACTIVITIES_BKPF are merged into the corresponding cases of BSEG. The resulting (internal) table derived from the query is joined to BSEG.

Query

Column1

"BSEG"."CASE_ID"

Column2

MERGE_EVENTLOG ( "ACTIVITIES_BSEG"."ACTIVITY" , "ACTIVITIES_BKPF"."ACTIVITY" )

Input

Output

ACTIVITIES_BKPF

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'100'

'Vendor creates Invoice'

Mon Feb 01 2016 01:00:00.000

'100'

'Enter in SAP'

Mon Feb 01 2016 02:00:00.000

'200'

'Vendor creates Invoice'

Mon Feb 01 2016 01:00:00.000

'200'

'Enter in SAP'

Mon Feb 01 2016 02:00:00.000

ACTIVITIES_BSEG

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'10001'

'Due Date passed'

Mon Feb 01 2016 03:00:00.000

'10001'

'Clear Invoice'

Mon Feb 01 2016 04:00:00.000

'10002'

'Due Date passed'

Mon Feb 01 2016 05:00:00.000

'10002'

'Clear Invoice'

Mon Feb 01 2016 06:00:00.000

'20001'

'Due Date passed'

Mon Feb 01 2016 03:00:00.000

'20001'

'Clear Invoice'

Mon Feb 01 2016 04:00:00.000

BKPF

CASE_ID : STRING

NETWR : FLOAT

'100'

5.0

'200'

10.0

BSEG

CASE_ID : STRING

SUPER_CASE_ID : STRING

'10001'

'100'

'20001'

'200'

'10002'

'100'

Foreign Keys

BKPF.CASE_ID

ACTIVITIES_BKPF.CASE_ID

BSEG.CASE_ID

ACTIVITIES_BSEG.CASE_ID

BKPF.CASE_ID

BSEG.SUPER_CASE_ID

Result

Column1 : STRING

Column2 : STRING

'10001'

'Vendor creates Invoice'

'10001'

'Enter in SAP'

'10001'

'Due Date passed'

'10001'

'Clear Invoice'

'10002'

'Vendor creates Invoice'

'10002'

'Enter in SAP'

'10002'

'Due Date passed'

'10002'

'Clear Invoice'

'20001'

'Vendor creates Invoice'

'20001'

'Enter in SAP'

'20001'

'Due Date passed'

'20001'

'Clear Invoice'

[2] This example scenario contains two activity tables ACTIVITIES_BSEG and ACTIVITIES_BKPF joined to their corresponding case tables BSEG and BKPF. The relationship between BSEG and BKPF is n:1. In the example query, the activity column from table ACTIVITIES_BKPF is the first input parameter. Therefore, the activities from table ACTIVITIES_BSEG are merged into the corresponding cases of BKPF. The resulting (internal) table derived from the query is joined to BKPF.

Query

Column1

"BKPF"."CASE_ID"

Column2

MERGE_EVENTLOG ( "ACTIVITIES_BKPF"."ACTIVITY" , "ACTIVITIES_BSEG"."ACTIVITY" )

Input

Output

ACTIVITIES_BKPF

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'100'

'Vendor creates Invoice'

Mon Feb 01 2016 01:00:00.000

'100'

'Enter in SAP'

Mon Feb 01 2016 02:00:00.000

'200'

'Vendor creates Invoice'

Mon Feb 01 2016 01:00:00.000

'200'

'Enter in SAP'

Mon Feb 01 2016 02:00:00.000

ACTIVITIES_BSEG

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'10001'

'Due Date passed'

Mon Feb 01 2016 03:00:00.000

'10001'

'Clear Invoice'

Mon Feb 01 2016 04:00:00.000

'10002'

'Due Date passed'

Mon Feb 01 2016 05:00:00.000

'10002'

'Clear Invoice'

Mon Feb 01 2016 06:00:00.000

'20001'

'Due Date passed'

Mon Feb 01 2016 03:00:00.000

'20001'

'Clear Invoice'

Mon Feb 01 2016 04:00:00.000

BKPF

CASE_ID : STRING

NETWR : FLOAT

'100'

5.0

'200'

10.0

BSEG

CASE_ID : STRING

SUPER_CASE_ID : STRING

'10001'

'100'

'20001'

'200'

'10002'

'100'

Foreign Keys

BKPF.CASE_ID

ACTIVITIES_BKPF.CASE_ID

BSEG.CASE_ID

ACTIVITIES_BSEG.CASE_ID

BKPF.CASE_ID

BSEG.SUPER_CASE_ID

Result

Column1 : STRING

Column2 : STRING

'100'

'Vendor creates Invoice'

'100'

'Enter in SAP'

'100'

'Due Date passed'

'100'

'Clear Invoice'

'100'

'Due Date passed'

'100'

'Clear Invoice'

'200'

'Vendor creates Invoice'

'200'

'Enter in SAP'

'200'

'Due Date passed'

'200'

'Clear Invoice'

[3] In this scenario, the two case tables 'CASE_TABLE_RIGHT' and 'CASE_TABLE_LEFT' are in a n:m relationship that is modeled via two 1:n relationships and the intermediate table 'LEFT_TO_RIGHT'. By using MERGE_EVENTLOG with columns from activity table 'ACTIVITIES_TABLE_RIGHT' as first input column, all activities from activities table 'ACTIVITIES_TABLE_LEFT' are merged to every matching case from 'CASE_TABLE_RIGHT'. The resulting (internal) merge table is joined to the case table 'CASE_TABLE_RIGHT'.

Query

Column1

CASE_ID_COLUMN (
    MERGE_EVENTLOG ( "ACTIVITIES_TABLE_RIGHT"."ACTIVITY" , "ACTIVITIES_TABLE_LEFT"."ACTIVITY" )
)

Column2

CASE_ID_COLUMN (
    MERGE_EVENTLOG ( "ACTIVITIES_TABLE_RIGHT"."ACTIVITY" , "ACTIVITIES_TABLE_LEFT"."ACTIVITY" )
)

Column3

TIMESTAMP_COLUMN (
    MERGE_EVENTLOG ( "ACTIVITIES_TABLE_RIGHT"."ACTIVITY" , "ACTIVITIES_TABLE_LEFT"."ACTIVITY" )
)

Input

Output

ACTIVITIES_TABLE_LEFT

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'CL1'

'A'

Mon Feb 01 2016 02:00:00.000

'CL1'

'B'

Mon Feb 01 2016 03:00:00.000

'CL2'

'C'

Mon Feb 01 2016 04:00:00.000

'CL3'

'D'

Mon Feb 01 2016 00:00:00.000

'CL3'

'A'

Mon Feb 01 2016 01:00:00.000

'CL4'

'B'

Mon Feb 01 2016 05:00:00.000

'CL4'

'C'

Mon Feb 01 2016 08:00:00.000

ACTIVITIES_TABLE_RIGHT

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'CR1'

'E'

Mon Feb 01 2016 00:00:00.000

'CR1'

'F'

Mon Feb 01 2016 01:00:00.000

'CR2'

'G'

Mon Feb 01 2016 05:00:00.000

'CR3'

'H'

Mon Feb 01 2016 02:00:00.000

'CR4'

'F'

Mon Feb 01 2016 06:00:00.000

'CR4'

'G'

Mon Feb 01 2016 07:00:00.000

CASE_TABLE_LEFT

CASE_ID : STRING

'CL1'

'CL2'

'CL3'

'CL4'

CASE_TABLE_RIGHT

CASE_ID : STRING

'CR1'

'CR2'

'CR3'

'CR4'

LEFT_TO_RIGHT

CASE_ID_LEFT : STRING

CASE_ID_RIGHT : STRING

'CL1'

'CR1'

'CL1'

'CR3'

'CL2'

'CR2'

'CL2'

'CR4'

'CL3'

'CR1'

'CL3'

'CR3'

'CL4'

'CR2'

'CL4'

'CR4'

Foreign Keys

CASE_TABLE_LEFT.CASE_ID

LEFT_TO_RIGHT.CASE_ID_LEFT

CASE_TABLE_RIGHT.CASE_ID

LEFT_TO_RIGHT.CASE_ID_RIGHT

CASE_TABLE_LEFT.CASE_ID

ACTIVITIES_TABLE_LEFT.CASE_ID

CASE_TABLE_RIGHT.CASE_ID

ACTIVITIES_TABLE_RIGHT.CASE_ID

Result

Column1 : STRING

Column2 : STRING

Column3 : DATE

'CR1'

'E'

Mon Feb 01 2016 00:00:00.000

'CR1'

'D'

Mon Feb 01 2016 00:00:00.000

'CR1'

'F'

Mon Feb 01 2016 01:00:00.000

'CR1'

'A'

Mon Feb 01 2016 01:00:00.000

'CR1'

'A'

Mon Feb 01 2016 02:00:00.000

'CR1'

'B'

Mon Feb 01 2016 03:00:00.000

'CR2'

'C'

Mon Feb 01 2016 04:00:00.000

'CR2'

'G'

Mon Feb 01 2016 05:00:00.000

'CR2'

'B'

Mon Feb 01 2016 05:00:00.000

'CR2'

'C'

Mon Feb 01 2016 08:00:00.000

'CR3'

'D'

Mon Feb 01 2016 00:00:00.000

'CR3'

'A'

Mon Feb 01 2016 01:00:00.000

'CR3'

'H'

Mon Feb 01 2016 02:00:00.000

'CR3'

'A'

Mon Feb 01 2016 02:00:00.000

'CR3'

'B'

Mon Feb 01 2016 03:00:00.000

'CR4'

'C'

Mon Feb 01 2016 04:00:00.000

'CR4'

'B'

Mon Feb 01 2016 05:00:00.000

'CR4'

'F'

Mon Feb 01 2016 06:00:00.000

'CR4'

'G'

Mon Feb 01 2016 07:00:00.000

'CR4'

'C'

Mon Feb 01 2016 08:00:00.000

[4] Like in the previous example, the two case tables 'CASE_TABLE_RIGHT' and 'CASE_TABLE_LEFT' are in a n:m relationship that is modeled via two 1:n relationships and the intermediate table 'LEFT_TO_RIGHT'. This time, MERGE_EVENTLOG is used with the activity column from activity table 'ACTIVITIES_TABLE_LEFT' as first input column. Therefore, all activities from activities table 'ACTIVITIES_TABLE_RIGHT' are merged to every matching case from 'CASE_TABLE_LEFT'. The resulting (internal) merge table is joined to the case table 'CASE_TABLE_LEFT'.

Query

Column1

CASE_ID_COLUMN (
    MERGE_EVENTLOG ( "ACTIVITIES_TABLE_LEFT"."ACTIVITY" , "ACTIVITIES_TABLE_RIGHT"."ACTIVITY" )
)

Column2

MERGE_EVENTLOG ( "ACTIVITIES_TABLE_LEFT"."ACTIVITY" , "ACTIVITIES_TABLE_RIGHT"."ACTIVITY" )

Column3

TIMESTAMP_COLUMN (
    MERGE_EVENTLOG ( "ACTIVITIES_TABLE_LEFT"."ACTIVITY" , "ACTIVITIES_TABLE_RIGHT"."ACTIVITY" )
)

Input

Output

ACTIVITIES_TABLE_LEFT

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'CL1'

'A'

Mon Feb 01 2016 02:00:00.000

'CL1'

'B'

Mon Feb 01 2016 03:00:00.000

'CL2'

'C'

Mon Feb 01 2016 04:00:00.000

'CL3'

'D'

Mon Feb 01 2016 00:00:00.000

'CL3'

'A'

Mon Feb 01 2016 01:00:00.000

'CL4'

'B'

Mon Feb 01 2016 05:00:00.000

'CL4'

'C'

Mon Feb 01 2016 08:00:00.000

ACTIVITIES_TABLE_RIGHT

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'CR1'

'E'

Mon Feb 01 2016 00:00:00.000

'CR1'

'F'

Mon Feb 01 2016 01:00:00.000

'CR2'

'G'

Mon Feb 01 2016 05:00:00.000

'CR3'

'H'

Mon Feb 01 2016 02:00:00.000

'CR4'

'F'

Mon Feb 01 2016 06:00:00.000

'CR4'

'G'

Mon Feb 01 2016 07:00:00.000

CASE_TABLE_LEFT

CASE_ID : STRING

'CL1'

'CL2'

'CL3'

'CL4'

CASE_TABLE_RIGHT

CASE_ID : STRING

'CR1'

'CR2'

'CR3'

'CR4'

LEFT_TO_RIGHT

CASE_ID_LEFT : STRING

CASE_ID_RIGHT : STRING

'CL1'

'CR1'

'CL1'

'CR3'

'CL2'

'CR2'

'CL2'

'CR4'

'CL3'

'CR1'

'CL3'

'CR3'

'CL4'

'CR2'

'CL4'

'CR4'

Foreign Keys

CASE_TABLE_LEFT.CASE_ID

LEFT_TO_RIGHT.CASE_ID_LEFT

CASE_TABLE_RIGHT.CASE_ID

LEFT_TO_RIGHT.CASE_ID_RIGHT

CASE_TABLE_LEFT.CASE_ID

ACTIVITIES_TABLE_LEFT.CASE_ID

CASE_TABLE_RIGHT.CASE_ID

ACTIVITIES_TABLE_RIGHT.CASE_ID

Result

Column1 : STRING

Column2 : STRING

Column3 : DATE

'CL1'

'E'

Mon Feb 01 2016 00:00:00.000

'CL1'

'F'

Mon Feb 01 2016 01:00:00.000

'CL1'

'A'

Mon Feb 01 2016 02:00:00.000

'CL1'

'H'

Mon Feb 01 2016 02:00:00.000

'CL1'

'B'

Mon Feb 01 2016 03:00:00.000

'CL2'

'C'

Mon Feb 01 2016 04:00:00.000

'CL2'

'G'

Mon Feb 01 2016 05:00:00.000

'CL2'

'F'

Mon Feb 01 2016 06:00:00.000

'CL2'

'G'

Mon Feb 01 2016 07:00:00.000

'CL3'

'D'

Mon Feb 01 2016 00:00:00.000

'CL3'

'E'

Mon Feb 01 2016 00:00:00.000

'CL3'

'A'

Mon Feb 01 2016 01:00:00.000

'CL3'

'F'

Mon Feb 01 2016 01:00:00.000

'CL3'

'H'

Mon Feb 01 2016 02:00:00.000

'CL4'

'B'

Mon Feb 01 2016 05:00:00.000

'CL4'

'G'

Mon Feb 01 2016 05:00:00.000

'CL4'

'F'

Mon Feb 01 2016 06:00:00.000

'CL4'

'G'

Mon Feb 01 2016 07:00:00.000

'CL4'

'C'

Mon Feb 01 2016 08:00:00.000