Skip to main content

Celonis Product Documentation

TRANSIT_COLUMN
Description

TRANSIT_COLUMN computes transition edges between related cases from two different processes.

The TRANSIT_COLUMN operator provides transitions showing where activities of two processes interact. The type of interaction is defined by the miner that is placed as input to the TRANSIT_COLUMN operator. All available miners are described in detail below in the miner section. For calculating transitions between two cases, the corresponding tables have to be linked in the Data Model Editor. In general, there are two scenarios how the activity tables can be linked in the data model. The following section describes these two scenarios.

1:N Scenario

Given two activity tables whose case tables are connected directly or indirectly via a 1:N relationship, the transit column operator computes the edges that visualize the interaction between the two related cases. The resulting columns belong to special edge tables ('Transit Table of Activity Table 1' and 'Transit Table of Activity Table N' in the figures below) that are joined to corresponding activity tables. One row in the edge table corresponds to one transition.

1NScenario_1_TransitColumn.png

The two case tables can also be identical like shown in the following picture.

1NScenario_2_TransitColumn.png
N:M Scenario

Given two activity tables ('Activity Table Left' and 'Activity Table Right') whose case tables ('Case Table Left' and 'Case Table Right') are in a N:M relationship which is e.g. modeled via two 1:N relationships and an intermediate table 'Intermediate Table' as shown below, the TRANSIT_COLUMN operator computes the edges that visualize the interaction between the two related cases. The resulting columns belong to special edge tables ('Transit Table of Activity Table Left' and 'Transit Table of Activity Table Right') that are joined to corresponding activity tables. One row in the edge table corresponds to one transition.

NMScenario_TransitColumn.png

Given a more complex N:M scenario shown below, the N:M relationship is modeled via multiple intermediate tables ('Intermediate Table A', 'Intermediate Table B' and 'Intermediate Table C') and the join from 'Case Table Left' to 'Case Table Right' is calculated transitively over these intermediate tables. TRANSIT_COLUMN computes the edges that visualize the interaction between the two related cases. The resulting columns belong to special edge tables ('Transit Table of Activity Table Left' and 'Transit Table of Activity Table Right' in the figure below) that are joined to corresponding activity tables. One row in the edge table corresponds to one transition. This behavior is analog to the simpler N:M case described above.

NMComplexScenario_TransitColumn.png
Miners

Currently, there are four miners implemented. Two miners compute the edges based on the timestamps of the events in the related cases, the interleaved and the non-interleaved miner. For these miners, the direction of the edges can be retrieved by comparing the timestamps. The third miner computes the edges based on an additional message and is called the match miner. The fourth miner computes the edges based on manually defined connections.

Tie resolution on timestamps: If both event logs have a sorting column of type integer defined, the sorting column is used to resolve ties. Otherwise or if there is still a tie, the following logic applies: For 1:N cases, the event on the rn side is treated as if it comes first. For N:M cases, the event on the left side (as specified by the miner parameters) is treated as if it comes first.

Interleaved miner

The interleaved miner outputs an edge for every transition from one case to the other.

transit_interleaved_miner.png
Syntax
 TRANSIT_COLUMN(TIMESTAMP_INTERLEAVED_MINER(activity_table_a.activity_column, activity_table_b.activity_column), activity_column )

  • activity_column: A column of activity table activity_table_a or activity_table_b.

Example

[1]

TRANSIT_COLUMN calculates, based on the TIMESTAMP_INTERLEAVED_MINER, all transition edges between related cases in a 1:N scenario. Therefore, the TIMESTAMP_INTERLEAVED_MINER calculates all transition edges based on the activity columns "E_RN"."ACTIVITY" and "E_R1"."ACTIVITY". As the activity column from the n side is passed as second argument to the TRANSIT_COLUMN operator, it returns the resulting column from the transit edge table from the n side.

Query

Column1 

         "E_R1"."ACTIVITY"

Column2 

         TRANSIT_COLUMN ( TIMESTAMP_INTERLEAVED_MINER ( "E_RN"."ACTIVITY" , "E_R1"."ACTIVITY" ) , "E_RN"."ACTIVITY" )

Input

Output

C_R1

CASE_ID : string

NETWR : float

'CI1'

5.0

'CI2'

6.0

C_RN

CASE_ID : string

SUPER_CASE_ID : string

'CI1a'

'CI1'

'CI1b'

'CI1'

'CI2a'

'CI2'

'CI2b'

'CI2'

E_R1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

IN_MSG : string

'CI1'

'A'

Mon Feb 01 2016 02:00:00.000

null

'CI1'

'B'

Mon Feb 01 2016 03:00:00.000

'PN1025'

'CI1'

'C'

Mon Feb 01 2016 04:00:00.000

null

'CI2'

'D'

Mon Feb 01 2016 00:00:00.000

null

'CI2'

'A'

Mon Feb 01 2016 01:00:00.000

null

'CI2'

'B'

Mon Feb 01 2016 05:00:00.000

'PN1025'

'CI2'

'C'

Mon Feb 01 2016 08:00:00.000

null

E_RN

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT_MSG : string

'CI1a'

'E'

Mon Feb 01 2016 00:00:00.000

'PN1025'

'CI1a'

'F'

Mon Feb 01 2016 01:00:00.000

null

'CI1b'

'G'

Mon Feb 01 2016 05:00:00.000

null

'CI2a'

'H'

Mon Feb 01 2016 02:00:00.000

'PN1025'

'CI2b'

'F'

Mon Feb 01 2016 06:00:00.000

null

'CI2b'

'G'

Mon Feb 01 2016 07:00:00.000

null

Foreign Keys

C_R1.CASE_ID

E_R1.CASE_ID

C_RN.CASE_ID

E_RN.CASE_ID

C_R1.CASE_ID

C_RN.SUPER_CASE_ID

Result

Column1 : string

Column2 : string

'A'

'F'

'C'

'G'

'A'

'H'

'B'

'H'

'B'

'F'

'C'

'G'

[2]

TRANSIT_COLUMN calculates, based on the TIMESTAMP_INTERLEAVED_MINER, all transition edges between related cases in a 1:N scenario. Therefore, the TIMESTAMP_INTERLEAVED_MINER calculates all transition edges based on the activity columns "E_RN"."ACTIVITY" and "E_R1"."ACTIVITY". As the activity column from the 1 side is passed as second argument to the TRANSIT_COLUMN operator, it returns the resulting column from the transit edge table from the 1 side.

Query

Column1 

         "E_RN"."ACTIVITY"

Column2 

         TRANSIT_COLUMN ( TIMESTAMP_INTERLEAVED_MINER ( "E_RN"."ACTIVITY" , "E_R1"."ACTIVITY" ) , "E_R1"."ACTIVITY" )

Input

Output

C_R1

CASE_ID : string

NETWR : float

'CI1'

5.0

'CI2'

6.0

C_RN

CASE_ID : string

SUPER_CASE_ID : string

'CI1a'

'CI1'

'CI1b'

'CI1'

'CI2a'

'CI2'

'CI2b'

'CI2'

E_R1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

IN_MSG : string

'CI1'

'A'

Mon Feb 01 2016 02:00:00.000

null

'CI1'

'B'

Mon Feb 01 2016 03:00:00.000

'PN1025'

'CI1'

'C'

Mon Feb 01 2016 04:00:00.000

null

'CI2'

'D'

Mon Feb 01 2016 00:00:00.000

null

'CI2'

'A'

Mon Feb 01 2016 01:00:00.000

null

'CI2'

'B'

Mon Feb 01 2016 05:00:00.000

'PN1025'

'CI2'

'C'

Mon Feb 01 2016 08:00:00.000

null

E_RN

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT_MSG : string

'CI1a'

'E'

Mon Feb 01 2016 00:00:00.000

'PN1025'

'CI1a'

'F'

Mon Feb 01 2016 01:00:00.000

null

'CI1b'

'G'

Mon Feb 01 2016 05:00:00.000

null

'CI2a'

'H'

Mon Feb 01 2016 02:00:00.000

'PN1025'

'CI2b'

'F'

Mon Feb 01 2016 06:00:00.000

null

'CI2b'

'G'

Mon Feb 01 2016 07:00:00.000

null

Foreign Keys

C_R1.CASE_ID

E_R1.CASE_ID

C_RN.CASE_ID

E_RN.CASE_ID

C_R1.CASE_ID

C_RN.SUPER_CASE_ID

Result

Column1 : string

Column2 : string

'F'

'A'

'G'

'C'

'H'

'A'

'H'

'B'

'F'

'B'

'G'

'C'

[3]

TRANSIT_COLUMN calculates, based on the TIMESTAMP_INTERLEAVED_MINER, all transition edges between related cases in a N:M scenario. Therefore, the TIMESTAMP_INTERLEAVED_MINER calculates all transition edges based on the activity columns "ACTIVITIES_TABLE_LEFT"."ACTIVITY" and "ACTIVITIES_TABLE_RIGHT"."ACTIVITY". As the case ID column concatenated with the activity column from the right side is passed as second argument to the TRANSIT_COLUMN operator, it returns the resulting column from the transit edge table from the right side.

Query

Column1 

         "ACTIVITIES_TABLE_LEFT"."CASE_ID" || ':' || "ACTIVITIES_TABLE_LEFT"."ACTIVITY"

Column2 

         TRANSIT_COLUMN ( TIMESTAMP_INTERLEAVED_MINER ( "ACTIVITIES_TABLE_LEFT"."ACTIVITY" , "ACTIVITIES_TABLE_RIGHT"."ACTIVITY" ) , "ACTIVITIES_TABLE_RIGHT"."CASE_ID" || ':' || "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

'CL1:A'

'CR1:F'

'CL3:D'

'CR1:E'

'CL3:A'

'CR1:E'

'CL3:A'

'CR1:F'

'CL2:C'

'CR2:G'

'CL4:B'

'CR2:G'

'CL4:C'

'CR2:G'

'CL1:A'

'CR3:H'

'CL1:B'

'CR3:H'

'CL3:A'

'CR3:H'

'CL2:C'

'CR4:F'

'CL4:B'

'CR4:F'

'CL4:C'

'CR4:G'

[4]

TRANSIT_COLUMN calculates, based on the TIMESTAMP_INTERLEAVED_MINER, all transition edges between related cases in a N:M scenario. Therefore, the TIMESTAMP_INTERLEAVED_MINER calculates all transition edges based on the activity columns "ACTIVITIES_TABLE_LEFT"."ACTIVITY" and "ACTIVITIES_TABLE_RIGHT"."ACTIVITY". As the case ID column concatenated with the activity column from the right side is passed as second argument to the TRANSIT_COLUMN operator, it returns the resulting column from the transit edge table from the right side.

Query

Column1 

         "ACTIVITIES_TABLE_LEFT"."CASE_ID" || ':' || "ACTIVITIES_TABLE_LEFT"."ACTIVITY"

Column2 

         TRANSIT_COLUMN ( TIMESTAMP_INTERLEAVED_MINER ( "ACTIVITIES_TABLE_RIGHT"."ACTIVITY" , "ACTIVITIES_TABLE_LEFT"."ACTIVITY" ) , "ACTIVITIES_TABLE_RIGHT"."CASE_ID" || ':' || "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

'CL1:A'

'CR1:F'

'CL1:A'

'CR3:H'

'CL2:C'

'CR2:G'

'CL2:C'

'CR4:F'

'CL3:D'

'CR1:E'

'CL3:D'

'CR1:F'

'CL3:A'

'CR1:F'

'CL3:A'

'CR3:H'

'CL4:B'

'CR2:G'

'CL4:B'

'CR4:F'

'CL4:C'

'CR4:G'

Non-interleaved miner

The non-interleaved miner outputs the first transition (same edge as interleaved miner). Furthermore, the last transition in the opposite direction is output if such a transition exists.

transit_noninterleaved_miner.png
Syntax
 TRANSIT_COLUMN(TIMESTAMP_NONINTERLEAVED_MINER(activity_table_a.activity_column, activity_table_b.activity_column), activity_column )

  • activity_column: A column of activity table activity_table_a or activity_table_b.

Example

[5]

TRANSIT_COLUMN calculates, based on the TIMESTAMP_NONINTERLEAVED_MINER, the first transition edge and, if such a transition exists, the last transition edge in the opposite direction between related cases in a 1:N scenario. Therefore, the TIMESTAMP_NONINTERLEAVED_MINER calculates the transition edges based on the activity columns "E_RN"."ACTIVITY" and "E_R1"."ACTIVITY". As the activity column from the n side is passed as second argument to the TRANSIT_COLUMN operator, it returns the resulting column from the transit edge table from the n side.

Query

Column1 

         "E_R1"."ACTIVITY"

Column2 

         TRANSIT_COLUMN ( TIMESTAMP_NONINTERLEAVED_MINER ( "E_RN"."ACTIVITY" , "E_R1"."ACTIVITY" ) , "E_RN"."ACTIVITY" )

Input

Output

C_R1

CASE_ID : string

NETWR : float

'CI1'

5.0

'CI2'

6.0

C_RN

CASE_ID : string

SUPER_CASE_ID : string

'CI1a'

'CI1'

'CI1b'

'CI1'

'CI2a'

'CI2'

'CI2b'

'CI2'

E_R1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

IN_MSG : string

'CI1'

'A'

Mon Feb 01 2016 02:00:00.000

null

'CI1'

'B'

Mon Feb 01 2016 03:00:00.000

'PN1025'

'CI1'

'C'

Mon Feb 01 2016 04:00:00.000

null

'CI2'

'D'

Mon Feb 01 2016 00:00:00.000

null

'CI2'

'A'

Mon Feb 01 2016 01:00:00.000

null

'CI2'

'B'

Mon Feb 01 2016 05:00:00.000

'PN1025'

'CI2'

'C'

Mon Feb 01 2016 08:00:00.000

null

E_RN

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT_MSG : string

'CI1a'

'E'

Mon Feb 01 2016 00:00:00.000

'PN1025'

'CI1a'

'F'

Mon Feb 01 2016 01:00:00.000

null

'CI1b'

'G'

Mon Feb 01 2016 05:00:00.000

null

'CI2a'

'H'

Mon Feb 01 2016 02:00:00.000

'PN1025'

'CI2b'

'F'

Mon Feb 01 2016 06:00:00.000

null

'CI2b'

'G'

Mon Feb 01 2016 07:00:00.000

null

Foreign Keys

C_R1.CASE_ID

E_R1.CASE_ID

C_RN.CASE_ID

E_RN.CASE_ID

C_R1.CASE_ID

C_RN.SUPER_CASE_ID

Result

Column1 : string

Column2 : string

'A'

'F'

'C'

'G'

'A'

'H'

'B'

'H'

'B'

'F'

'C'

'G'

Match miner

The match miner outputs an edge for every matched message from one case.

transit_match_miner.png
Syntax
 TRANSIT_COLUMN(MATCH_MINER(activity_table_a.activity_column, activity_table_b.activity_column, activity_table_a.out_msg, activity_table_b.in_msg), activity_column )

  • activity_column: A column of activity table activity_table_a or activity_table_b.

Example

[6]

TRANSIT_COLUMN calculates, based on the MATCH_MINER, the transition edges for every matched message from related cases in a 1:N scenario. Therefore, the MATCH_MINER calculates the transition edges based on the activity columns "E_RN"."ACTIVITY" and "E_R1"."ACTIVITY" as well as the in and out messages from "E_R1"."IN_MSG" and "E_RN"."OUT_MSG". As the activity column from the 1 side is passed as second argument to the TRANSIT_COLUMN operator, it returns the resulting column from the transit edge table from the 1 side.

Query

Column1 

         "E_RN"."ACTIVITY"

Column2 

         TRANSIT_COLUMN ( MATCH_MINER ( "E_RN"."ACTIVITY" , "E_R1"."ACTIVITY" , "E_RN"."OUT_MSG" , "E_R1"."IN_MSG" ) , "E_R1"."ACTIVITY" )

Input

Output

C_R1

CASE_ID : string

NETWR : float

'CI1'

5.0

'CI2'

6.0

C_RN

CASE_ID : string

SUPER_CASE_ID : string

'CI1a'

'CI1'

'CI1b'

'CI1'

'CI2a'

'CI2'

'CI2b'

'CI2'

E_R1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

IN_MSG : string

'CI1'

'A'

Mon Feb 01 2016 02:00:00.000

null

'CI1'

'B'

Mon Feb 01 2016 03:00:00.000

'PN1025'

'CI1'

'C'

Mon Feb 01 2016 04:00:00.000

null

'CI2'

'D'

Mon Feb 01 2016 00:00:00.000

null

'CI2'

'A'

Mon Feb 01 2016 01:00:00.000

null

'CI2'

'B'

Mon Feb 01 2016 05:00:00.000

'PN1025'

'CI2'

'C'

Mon Feb 01 2016 08:00:00.000

null

E_RN

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT_MSG : string

'CI1a'

'E'

Mon Feb 01 2016 00:00:00.000

'PN1025'

'CI1a'

'F'

Mon Feb 01 2016 01:00:00.000

null

'CI1b'

'G'

Mon Feb 01 2016 05:00:00.000

null

'CI2a'

'H'

Mon Feb 01 2016 02:00:00.000

'PN1025'

'CI2b'

'F'

Mon Feb 01 2016 06:00:00.000

null

'CI2b'

'G'

Mon Feb 01 2016 07:00:00.000

null

Foreign Keys

C_R1.CASE_ID

E_R1.CASE_ID

C_RN.CASE_ID

E_RN.CASE_ID

C_R1.CASE_ID

C_RN.SUPER_CASE_ID

Result

Column1 : string

Column2 : string

'E'

'B'

'H'

'B'

[7]

TRANSIT_COLUMN calculates, based on the MATCH_MINER, the transition edges for every matched message from related cases in a N:M scenario. Therefore, the MATCH_MINER calculates the transition edges based on the activity columns "ACTIVITIES_TABLE_LEFT"."ACTIVITY" and "ACTIVITIES_TABLE_RIGHT"."ACTIVITY" as well as the in and out messages from "ACTIVITIES_TABLE_LEFT"."LEFT_MSG" and "ACTIVITIES_TABLE_RIGHT"."RIGHT_MSG". As the case ID column concatenated with the activity column from the right side is passed as second argument to the TRANSIT_COLUMN operator, it returns the resulting column from the transit edge table from the right side.

Query

Column1 

         "ACTIVITIES_TABLE_LEFT"."CASE_ID" || ':' || "ACTIVITIES_TABLE_LEFT"."ACTIVITY"

Column2 

         TRANSIT_COLUMN ( MATCH_MINER ( "ACTIVITIES_TABLE_LEFT"."ACTIVITY" , "ACTIVITIES_TABLE_RIGHT"."ACTIVITY" , "ACTIVITIES_TABLE_LEFT"."LEFT_MSG" , "ACTIVITIES_TABLE_RIGHT"."RIGHT_MSG" ) , "ACTIVITIES_TABLE_RIGHT"."CASE_ID" || ':' || "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

'CL2:C'

'CR2:G'

'CL4:B'

'CR2:G'

'CL3:A'

'CR3:H'

'CL2:C'

'CR4:F'

'CL4:B'

'CR4:F'

Manual Miner

The manual miner outputs an edge for every manually set combination.

transit_manual_miner.png
Syntax
 TRANSIT_COLUMN(MANUAL_MINER(activity_table_a.activity_column, activity_table_b.activity_column, [manual_value_1, manual_value_2], ...), activity_column )

  • activity_column: A column of activity table activity_table_a or activity_table_b.

Examples

[8]

TRANSIT_COLUMN calculates, based on the MANUAL_MINER, the transition edges for every manually set combination from related cases in a 1:N scenario. Therefore, the MANUAL_MINER calculates the transition edges based on the activity columns "E_RN"."ACTIVITY" and "E_R1"."ACTIVITY" and the manually set combination ['F', 'B']. As the activity column from the n side is passed as second argument to the TRANSIT_COLUMN operator, it returns the resulting column from the transit edge table from the n side.

Query

Column1 

         "E_R1"."ACTIVITY"

Column2 

         TRANSIT_COLUMN ( MANUAL_MINER ( "E_RN"."ACTIVITY" , "E_R1"."ACTIVITY" , [ 'F' , 'B' ] ) , "E_RN"."ACTIVITY" )

Input

Output

C_R1

CASE_ID : string

NETWR : float

'CI1'

5.0

'CI2'

6.0

C_RN

CASE_ID : string

SUPER_CASE_ID : string

'CI1a'

'CI1'

'CI1b'

'CI1'

'CI2a'

'CI2'

'CI2b'

'CI2'

E_R1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

IN_MSG : string

'CI1'

'A'

Mon Feb 01 2016 02:00:00.000

null

'CI1'

'B'

Mon Feb 01 2016 03:00:00.000

'PN1025'

'CI1'

'C'

Mon Feb 01 2016 04:00:00.000

null

'CI2'

'D'

Mon Feb 01 2016 00:00:00.000

null

'CI2'

'A'

Mon Feb 01 2016 01:00:00.000

null

'CI2'

'B'

Mon Feb 01 2016 05:00:00.000

'PN1025'

'CI2'

'C'

Mon Feb 01 2016 08:00:00.000

null

E_RN

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT_MSG : string

'CI1a'

'E'

Mon Feb 01 2016 00:00:00.000

'PN1025'

'CI1a'

'F'

Mon Feb 01 2016 01:00:00.000

null

'CI1b'

'G'

Mon Feb 01 2016 05:00:00.000

null

'CI2a'

'H'

Mon Feb 01 2016 02:00:00.000

'PN1025'

'CI2b'

'F'

Mon Feb 01 2016 06:00:00.000

null

'CI2b'

'G'

Mon Feb 01 2016 07:00:00.000

null

Foreign Keys

C_R1.CASE_ID

E_R1.CASE_ID

C_RN.CASE_ID

E_RN.CASE_ID

C_R1.CASE_ID

C_RN.SUPER_CASE_ID

Result

Column1 : string

Column2 : string

'B'

'F'

'B'

'F'

[9]

TRANSIT_COLUMN calculates, based on the MANUAL_MINER, the transition edges for every manually set combination from related cases in a N:M scenario. Therefore, the MANUAL_MINER calculates the transition edges based on the activity columns "ACTIVITIES_TABLE_LEFT"."ACTIVITY" and "ACTIVITIES_TABLE_RIGHT"."ACTIVITY" and the manually set combination ['B', 'F'] and ['C', 'G']. As the activity column from the right side is passed as second argument to the TRANSIT_COLUMN operator, it returns the resulting column from the transit edge table from the right side.

Query

Column1 

         "ACTIVITIES_TABLE_LEFT"."CASE_ID" || ':' || "ACTIVITIES_TABLE_LEFT"."ACTIVITY"

Column2 

         TRANSIT_COLUMN ( MANUAL_MINER ( "ACTIVITIES_TABLE_LEFT"."ACTIVITY" , "ACTIVITIES_TABLE_RIGHT"."ACTIVITY" , [ 'B' , 'F' ] , [ 'C' , 'G' ] ) , "ACTIVITIES_TABLE_RIGHT"."CASE_ID" || ':' || "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

'CL1:B'

'CR1:F'

'CL2:C'

'CR2:G'

'CL4:C'

'CR2:G'

'CL2:C'

'CR4:G'

'CL4:B'

'CR4:F'

'CL4:C'

'CR4:G'