Skip to main content

Celonis Product Documentation

MERGE_EVENTLOG - MERGE_EVENTLOG_DISTINCT
Description

MERGE_EVENTLOG merges two columns of the same type from two activity tables into one common column. The sorting of entries in the resulting column is based on timestamps. Depending on the join scenario, the result of MERGE_EVENTLOG might contain some duplicated activities. If this is unwanted, then MERGE_EVENTLOG_DISTINCT can be used. Please note that MERGE_EVENTLOG and MERGE_EVENTLOG_DISTINCT only differ regarding duplicated activities and otherwise behave the same.

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 first column is the target column, the second column is the source column. The content of the source column is always merged into the corresponding cases of the target input column. Hence, the target cases will be enhanced with the entries of the source column.

This means that only cases that exist in the case table of the target column will be in the resulting eventlog table.

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

1NScenario_MergeEventlog_1.png

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

1NScenario_MergeEventlog_2.png
n:m and n:1:m Scenario

In case of a n:m or a n:1:m scenario,the join from 'Case Table Left' to 'Case Table Right' over the intermediate tables is calculated transitively. MERGE_EVENTLOG computes one combined (internal) table that is joined to the case table of the first input column. In the following examples, two activity tables 'Activity Table Left' and 'Activity Table Right' and their corresponding case tables 'Case Table Left' and 'Case Table Right' are given.

n:m Example

The case tables have a n:m relationship, which is modeled via two 1:n relationships and an intermediate table 'Intermediate Table'. The table created by MERGE_EVENTLOG 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'.

NMScenario_MergeEventlog.png
n:1:m Example

The case tables have a n:1:m scenario, which is modeled via two 1:n relationships and three intermediate tables 'Intermediate Table A', 'Intermediate Table B' and 'Intermediate Table C'. If the first input column is e.g. from 'Activity Table Left', the resulting (internal) 'Merge Table' is joined to 'Case Table Left'.

N1MScenario_MergeEventlog.png
Syntax
 MERGE_EVENTLOG ( target_table.column [, FILTER target_table_filter_expression ] , source_table.column [, FILTER source_table_filter_expression ] )

 MERGE_EVENTLOG_DISTINCT ( target_table.column [, FILTER target_table_filter_expression ] , source_table.column [, FILTER source_table_filter_expression ] )

  • target_table.column: A column of an activity table.

  • target_table_filter_expression (optional): An optional filter expression to specify which rows of the target_table should be taken into account for the resulting merged eventlog.

  • source_table.column: A column of an activity table.

  • source_table_filter_expression (optional): An optional filter expression to specify which rows of the source_table should be taken into account for the resulting merged eventlog.

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.

Limitations

The current limitations documented in Engine Limitations also hold for the result of MERGE_EVENTLOG. This means that the hard upper limit of rows for the result column of MERGE_EVENTLOG is 2.1 billion rows, and the recommended limit of rows is 800 million rows.

Special cases

  • Activities, that do not have a corresponding case id in the case table are not taken into account.

  • If an activity of the target table has a join partner in the source table but its case id is not in the target case table, the mapped activities are not taken into account.

  • If an activity of the source table has a join partner in the target table but its case id is not in the source case table, the target activity is in the resulting merged eventlog but the activities of the source table are ignored.

  • If there is a case id in the target case table but no corresponding activity in the target table and there is a join partner in the source table, the case id for for the merged activities from the source table will be generated. See section "Case ID Generation" for more information.

Peculiarities

  • The three required columns of every activity table (CaseID, Activity, Timestamp) are always implicitly merged in the background. Therefore, they can be referenced from any MERGE_EVENTLOG result using the CASE_ID_COLUMN, ACTIVITY_COLUMN and TIMESTAMP_COLUMN reference functions. It is also possible to automatically reference the corresponding case table of the MERGE_EVENTLOG result using the CASE_TABLE function.

  • MERGE_EVENTLOG is used for the automatic merge functionality described in Automerge

  • If both event logs have defined a sorting column, the resulting event log respects the merged sorting column for ordering events. The resulting event log also has a sorting column defined. If only one of the two event logs has a sorting column defined, the sorting column is ignored.

  • Timestamps of day-based activities from the timestamp input columns are never modified during the merge. This means that they stay the same even if the sorting rules for day based activities as described in activity table sorting would apply to the resulting event log.

Case ID Generation

In certain cases, it might happen that no case id for an activity from the merged eventlog is present in the target activity table. Due to the fact that join definitions between an activity table and a case table can generally be defined over multiple columns, we must generate new case ids. This is achieved by concatenating the columns of the target case table defining the join to the target activity table. If the generated case ids are not unique, a row number suffix will be additionally added.

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 

         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

LEFT_MSG : string

'CL1'

'A'

Mon Feb 01 2016 02:00:00.000

null

'CL1'

'B'

Mon Feb 01 2016 03:00:00.000

null

'CL2'

'C'

Mon Feb 01 2016 04:00:00.000

'N1'

'CL3'

'D'

Mon Feb 01 2016 00:00:00.000

null

'CL3'

'A'

Mon Feb 01 2016 01:00:00.000

'N3'

'CL4'

'B'

Mon Feb 01 2016 05:00:00.000

'N1'

'CL4'

'C'

Mon Feb 01 2016 08:00:00.000

null

ACTIVITIES_TABLE_RIGHT

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

RIGHT_MSG : string

'CR1'

'E'

Mon Feb 01 2016 00:00:00.000

null

'CR1'

'F'

Mon Feb 01 2016 01:00:00.000

null

'CR2'

'G'

Mon Feb 01 2016 05:00:00.000

'N1'

'CR3'

'H'

Mon Feb 01 2016 02:00:00.000

'N3'

'CR4'

'F'

Mon Feb 01 2016 06:00:00.000

'N1'

'CR4'

'G'

Mon Feb 01 2016 07:00:00.000

null

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_RIGHT.CASE_ID

ACTIVITIES_TABLE_RIGHT.CASE_ID

CASE_TABLE_LEFT.CASE_ID

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

LEFT_MSG : string

'CL1'

'A'

Mon Feb 01 2016 02:00:00.000

null

'CL1'

'B'

Mon Feb 01 2016 03:00:00.000

null

'CL2'

'C'

Mon Feb 01 2016 04:00:00.000

'N1'

'CL3'

'D'

Mon Feb 01 2016 00:00:00.000

null

'CL3'

'A'

Mon Feb 01 2016 01:00:00.000

'N3'

'CL4'

'B'

Mon Feb 01 2016 05:00:00.000

'N1'

'CL4'

'C'

Mon Feb 01 2016 08:00:00.000

null

ACTIVITIES_TABLE_RIGHT

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

RIGHT_MSG : string

'CR1'

'E'

Mon Feb 01 2016 00:00:00.000

null

'CR1'

'F'

Mon Feb 01 2016 01:00:00.000

null

'CR2'

'G'

Mon Feb 01 2016 05:00:00.000

'N1'

'CR3'

'H'

Mon Feb 01 2016 02:00:00.000

'N3'

'CR4'

'F'

Mon Feb 01 2016 06:00:00.000

'N1'

'CR4'

'G'

Mon Feb 01 2016 07:00:00.000

null

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_RIGHT.CASE_ID

ACTIVITIES_TABLE_RIGHT.CASE_ID

CASE_TABLE_LEFT.CASE_ID

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

[5]

/* If both event logs have a sorting column defined, the merged sorting column is respected for the merge result.

Query

Column1 

         MERGE_EVENTLOG ( "ACTIVITY_1"."ACTIVITY" , "ACTIVITY_2"."ACTIVITY" )

Input

Output

ACTIVITY_1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

SORTING : int

'C1'

'E1'

Mon Feb 01 2016 00:00:00.000

20

'C1'

'E2'

Mon Feb 01 2016 00:00:00.000

10

ACTIVITY_2

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

SORTING : int

'C1'

'E3'

Mon Feb 01 2016 00:00:00.000

25

'C1'

'E4'

Mon Feb 01 2016 00:00:00.000

15

ACTIVITY_3

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

SORTING : int

'C1'

'E5'

Mon Feb 01 2016 00:00:00.000

35

'C1'

'E6'

Mon Feb 01 2016 00:00:00.000

5

CASE

CASE_ID : string

'C1'

'C2'

'C3'

'C4'

Foreign Keys

CASE.CASE_ID

ACTIVITY_1.CASE_ID

CASE.CASE_ID

ACTIVITY_2.CASE_ID

CASE.CASE_ID

ACTIVITY_3.CASE_ID

Result

Column1 : string

'E2'

'E4'

'E1'

'E3'

[6]

This scenario shows that if there is no join partner in the source case table, the activities of the target table are still taken into account. Activities that are not connected to any case in the corresponding case table are ignored.

Query

Column1 

         "CASE_RIGHT"."CASE_ID"

Column2 

         MERGE_EVENTLOG ( "EVENTLOG_RIGHT"."ACTIVITY" , "EVENTLOG_LEFT"."ACTIVITY" )

Input

Output

CASE_LEFT

CASE_ID : string

NETWR : float

'100'

5.0

CASE_RIGHT

CASE_ID : string

SUPER_CASE_ID : string

'10001'

'100'

'10002'

'200'

EVENTLOG_LEFT

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'100'

'A'

Mon Feb 01 2016 01:00:00.000

'200'

'B'

Mon Feb 01 2016 02:00:00.000

EVENTLOG_RIGHT

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'10001'

'F'

Mon Feb 01 2016 03:00:00.000

'10002'

'G'

Mon Feb 01 2016 05:00:00.000

Foreign Keys

CASE_LEFT.CASE_ID

EVENTLOG_LEFT.CASE_ID

CASE_RIGHT.CASE_ID

EVENTLOG_RIGHT.CASE_ID

CASE_LEFT.CASE_ID

CASE_RIGHT.SUPER_CASE_ID

Result

Column1 : string

Column2 : string

'10001'

'A'

'10001'

'F'

'10002'

'G'

[7]

In this scenario one activity in the right eventlog does not have an entry in the corresponding case table. As a result, the activity does not show up in the resulting merged eventlog.

Query

Column1 

         "CASE_LEFT"."CASE_ID"

Column2 

         MERGE_EVENTLOG ( "EVENTLOG_LEFT"."ACTIVITY" , "EVENTLOG_RIGHT"."ACTIVITY" )

Input

Output

CASE_LEFT

CASE_ID : string

NETWR : float

'100'

5.0

CASE_RIGHT

CASE_ID : string

SUPER_CASE_ID : string

'10001'

'100'

EVENTLOG_LEFT

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'100'

'A'

Mon Feb 01 2016 01:00:00.000

EVENTLOG_RIGHT

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'10000'

'I have no corresponding entry in the case table#1'

Mon Feb 01 2016 01:00:00.000

'10001'

'F'

Mon Feb 01 2016 03:00:00.000

'10002'

'I have no corresponding entry in the case table#2'

Mon Feb 01 2016 05:00:00.000

Foreign Keys

CASE_LEFT.CASE_ID

EVENTLOG_LEFT.CASE_ID

CASE_RIGHT.CASE_ID

EVENTLOG_RIGHT.CASE_ID

CASE_LEFT.CASE_ID

CASE_RIGHT.SUPER_CASE_ID

Result

Column1 : string

Column2 : string

'100'

'A'

'100'

'F'

[8]

This scenario shows when there is no activity in the target table, but a join with the source table exists, then a case id is generated for the resulting merged eventlog.

Query

Column1 

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

Column2 

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

Input

Output

ACTIVITIES_BKPF

CASE_ID : string

JOIN_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'100'

'Vendor creates Invoice'

Mon Feb 01 2016 01:00:00.000

'2'

'200'

'Vendor creates Invoice'

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

'10002'

'Due Date passed'

Mon Feb 01 2016 05:00:00.000

'10003'

'Due Date passed'

Mon Feb 01 2016 07:00:00.000

BKPF

CASE_ID : string

NETWR : float

'100'

5.0

'200'

6.0

'300'

4.0

BSEG

CASE_ID : string

SUPER_CASE_ID : string

'10001'

'100'

'10002'

'200'

'10003'

'300'

Foreign Keys

BKPF.CASE_ID

ACTIVITIES_BKPF.JOIN_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'

'Due Date passed'

'200'

'Vendor creates Invoice'

'200'

'Due Date passed'

'300'

'Due Date passed'

[9]

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 target table and source table are filtered. Hence, only rows which pass the filters are considered in the resulting merged eventlog.

Query

Column1 

         "BSEG"."CASE_ID"

Column2 

         MERGE_EVENTLOG ( "ACTIVITIES_BSEG"."ACTIVITY" , FILTER "ACTIVITIES_BSEG"."ACTIVITY" IN_LIKE ( 'Clear Invoice' ) , "ACTIVITIES_BKPF"."ACTIVITY" , FILTER "ACTIVITIES_BKPF"."CASE_ID" = '100' AND "ACTIVITIES_BKPF"."ACTIVITY" IN ( 'Vendor creates Invoice' , 'Enter in SAP' ) )

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'

'Clear Invoice'

'10002'

'Vendor creates Invoice'

'10002'

'Enter in SAP'

'10002'

'Clear Invoice'

'20001'

'Clear Invoice'

[10]

This scenario shows the case id generation with multiple join columns. The join columns in the case table TARGET_CASE_TABLE are simply concatenated and use as case id.

Query

Column1 

         CASE_ID_COLUMN ( MERGE_EVENTLOG ( "TARGET_ACTIVITY_TABLE"."ACTIVITY" , "SOURCE_ACTIVITY_TABLE"."ACTIVITY" ) )

Column2 

         MERGE_EVENTLOG ( "TARGET_ACTIVITY_TABLE"."ACTIVITY" , "SOURCE_ACTIVITY_TABLE"."ACTIVITY" )

Input

Output

SOURCE_ACTIVITY_TABLE

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'10001'

'Activity 3'

Mon Feb 01 2016 03:00:00.000

SOURCE_CASE_TABLE

CASE_ID : string

JOIN_COLUMN_1 : string

JOIN_COLUMN_2 : string

'10001'

'Key2'

'KeyA'

TARGET_ACTIVITY_TABLE

CASE_ID : string

JOIN_COLUMN_1 : string

JOIN_COLUMN_2 : string

ACTIVITY : string

TIMESTAMP : date

'1'

'Key1'

'KeyA'

'Activity 1'

Mon Feb 01 2016 01:00:00.000

'1'

'Key1'

'KeyA'

'Activity 2'

Mon Feb 01 2016 02:00:00.000

TARGET_CASE_TABLE

JOIN_COLUMN_1 : string

JOIN_COLUMN_2 : string

'Key1'

'KeyA'

'Key2'

'KeyA'

Foreign Keys

TARGET_CASE_TABLE.JOIN_COLUMN_1

TARGET_ACTIVITY_TABLE.JOIN_COLUMN_1

TARGET_CASE_TABLE.JOIN_COLUMN_2

TARGET_ACTIVITY_TABLE.JOIN_COLUMN_2

SOURCE_CASE_TABLE.CASE_ID

SOURCE_ACTIVITY_TABLE.CASE_ID

SOURCE_CASE_TABLE.JOIN_COLUMN_1

TARGET_CASE_TABLE.JOIN_COLUMN_1

SOURCE_CASE_TABLE.JOIN_COLUMN_2

TARGET_CASE_TABLE.JOIN_COLUMN_2

Result

Column1 : string

Column2 : string

'Key1KeyA'

'Activity 1'

'Key1KeyA'

'Activity 2'

'Key2KeyA'

'Activity 3'

[11]

This scenario show the case id generation with multiple join columns. The concatenation of the join columns from the TARGET_CASE_TABLE are not unique because string concatenation returns NULL again if it receives at least one NULL as input. Since we have multiple rows which are evaluated to NULL, we have duplicates and not produced unique case ids. Therefore, we append the ruw number as suffix to make them unique again.

Query

Column1 

         CASE_ID_COLUMN ( MERGE_EVENTLOG ( "TARGET_ACTIVITY_TABLE"."ACTIVITY" , "SOURCE_ACTIVITY_TABLE"."ACTIVITY" ) )

Column2 

         MERGE_EVENTLOG ( "TARGET_ACTIVITY_TABLE"."ACTIVITY" , "SOURCE_ACTIVITY_TABLE"."ACTIVITY" )

Input

Output

SOURCE_ACTIVITY_TABLE

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'10001'

'Activity 3'

Mon Feb 01 2016 03:00:00.000

'10002'

'Activity 4'

Mon Feb 01 2016 04:00:00.000

'20001'

'Activity 5'

Tue Feb 02 2016 04:00:00.000

SOURCE_CASE_TABLE

CASE_ID : string

OTHER_JOIN_COLUMN_1 : string

'10001'

'200'

'10002'

'300'

'20001'

'400'

TARGET_ACTIVITY_TABLE

CASE_ID : string

JOIN_COLUMN_1 : string

JOIN_COLUMN_2 : string

ACTIVITY : string

TIMESTAMP : date

'1'

'Key1'

'KeyA'

'Activity 1'

Mon Feb 01 2016 01:00:00.000

'1'

'Key1'

'KeyA'

'Activity 2'

Mon Feb 01 2016 02:00:00.000

TARGET_CASE_TABLE

JOIN_COLUMN_1 : string

JOIN_COLUMN_2 : string

OTHER_JOIN_COLUMN_1 : string

'Key1'

'KeyA'

'100'

null

'KeyA'

'200'

'Key2'

null

'300'

null

null

'400'

Foreign Keys

TARGET_CASE_TABLE.JOIN_COLUMN_1

TARGET_ACTIVITY_TABLE.JOIN_COLUMN_1

TARGET_CASE_TABLE.JOIN_COLUMN_2

TARGET_ACTIVITY_TABLE.JOIN_COLUMN_2

SOURCE_CASE_TABLE.CASE_ID

SOURCE_ACTIVITY_TABLE.CASE_ID

SOURCE_CASE_TABLE.OTHER_JOIN_COLUMN_1

TARGET_CASE_TABLE.OTHER_JOIN_COLUMN_1

Result

Column1 : string

Column2 : string

'Key1KeyA-r=1'

'Activity 1'

'Key1KeyA-r=1'

'Activity 2'

'r=2'

'Activity 3'

'r=3'

'Activity 4'

'r=4'

'Activity 5'

[12]

Example to show that if a day based activity ("Enter in SAP") is on the same day as another activity after the merge, the timestamp of the day-based activity is not modified

Query

Column1 

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

Column2 

         ACTIVITY_COLUMN ( MERGE_EVENTLOG ( "ACTIVITIES_BKPF"."CASE_ID" , "ACTIVITIES_BSEG"."CASE_ID" ) )

Column3 

         TIMESTAMP_COLUMN ( MERGE_EVENTLOG ( "ACTIVITIES_BKPF"."CASE_ID" , "ACTIVITIES_BSEG"."CASE_ID" ) )

Input

Output

ACTIVITIES_BKPF

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

_SORTING : int

'100'

'Vendor creates Invoice'

Mon Feb 01 2016 01:00:00.000

1

'100'

'Enter in SAP'

Tue Feb 02 2016 00:00:00.000

2

'200'

'Vendor creates Invoice'

Mon Feb 01 2016 01:00:00.000

1

'200'

'Enter in SAP'

Tue Feb 02 2016 00:00:00.000

2

ACTIVITIES_BSEG

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

_SORTING : int

'10001'

'Due Date passed'

Tue Feb 02 2016 07:00:00.000

3

'10001'

'Clear Invoice'

Tue Feb 02 2016 08:00:00.000

4

'10002'

'Clear Invoice'

Tue Feb 02 2016 05:00:00.000

4

'10002'

'Due Date passed'

Tue Feb 02 2016 06:00:00.000

3

'20001'

'Due Date passed'

Tue Feb 02 2016 03:00:00.000

3

'20001'

'Clear Invoice'

Tue Feb 02 2016 04:00:00.000

4

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

Column3 : date

'100'

'Vendor creates Invoice'

Mon Feb 01 2016 01:00:00.000

'100'

'Enter in SAP'

Tue Feb 02 2016 00:00:00.000

'10002'

'Clear Invoice'

Tue Feb 02 2016 05:00:00.000

'10002'

'Due Date passed'

Tue Feb 02 2016 06:00:00.000

'10001'

'Due Date passed'

Tue Feb 02 2016 07:00:00.000

'10001'

'Clear Invoice'

Tue Feb 02 2016 08:00:00.000

'200'

'Vendor creates Invoice'

Mon Feb 01 2016 01:00:00.000

'200'

'Enter in SAP'

Tue Feb 02 2016 00:00:00.000

'20001'

'Due Date passed'

Tue Feb 02 2016 03:00:00.000

'20001'

'Clear Invoice'

Tue Feb 02 2016 04:00:00.000