Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

TRANSIT_COLUMN

Applies to: CELONIS 4.7

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 1' and 'Transit Table N' in the figure below) that are joined to corresponding activity tables. One row in the edge table corresponds to one transition.

34440403.png

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

34440404.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 that is e.g. modeled via two 1:n relationships and an intermediate table 'Fact Table Mid', 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 Left' and 'Transit Table Right' in the figure below) that are joined to corresponding activity tables. One row in the edge table corresponds to one transition.

34440405.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: For 1:n cases, if two events have the same timestamp, 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.

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

Sun Jan 01 2017 13:02:00.000

null

'CI1'

'B'

Sun Jan 01 2017 13:03:00.000

'PN1025'

'CI1'

'C'

Sun Jan 01 2017 13:04:00.000

null

'CI2'

'D'

Sun Jan 01 2017 13:00:00.000

null

'CI2'

'A'

Sun Jan 01 2017 13:01:00.000

null

'CI2'

'B'

Sun Jan 01 2017 13:05:00.000

'PN1025'

'CI2'

'C'

Sun Jan 01 2017 13:08:00.000

null

E_RN

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

OUT_MSG : STRING

'CI1a'

'E'

Sun Jan 01 2017 13:00:00.000

'PN1025'

'CI1a'

'F'

Sun Jan 01 2017 13:01:00.000

null

'CI1b'

'G'

Sun Jan 01 2017 13:05:00.000

null

'CI2a'

'H'

Sun Jan 01 2017 13:02:00.000

'PN1025'

'CI2b'

'F'

Sun Jan 01 2017 13:06:00.000

null

'CI2b'

'G'

Sun Jan 01 2017 13:07: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'

Sun Jan 01 2017 13:02:00.000

null

'CI1'

'B'

Sun Jan 01 2017 13:03:00.000

'PN1025'

'CI1'

'C'

Sun Jan 01 2017 13:04:00.000

null

'CI2'

'D'

Sun Jan 01 2017 13:00:00.000

null

'CI2'

'A'

Sun Jan 01 2017 13:01:00.000

null

'CI2'

'B'

Sun Jan 01 2017 13:05:00.000

'PN1025'

'CI2'

'C'

Sun Jan 01 2017 13:08:00.000

null

E_RN

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

OUT_MSG : STRING

'CI1a'

'E'

Sun Jan 01 2017 13:00:00.000

'PN1025'

'CI1a'

'F'

Sun Jan 01 2017 13:01:00.000

null

'CI1b'

'G'

Sun Jan 01 2017 13:05:00.000

null

'CI2a'

'H'

Sun Jan 01 2017 13:02:00.000

'PN1025'

'CI2b'

'F'

Sun Jan 01 2017 13:06:00.000

null

'CI2b'

'G'

Sun Jan 01 2017 13:07: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 m:n scenario. Therefore, the TIMESTAMP_INTERLEAVED_MINER calculates all transition edges based on the activity columns "E_RIGHT"."ACTIVITY" and "E_LEFT"."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

"E_LEFT"."CASE_ID" || ':' || "E_LEFT"."ACTIVITY"

Column2

TRANSIT_COLUMN (
    TIMESTAMP_INTERLEAVED_MINER ( "E_RIGHT"."ACTIVITY" , "E_LEFT"."ACTIVITY" ) ,
    "E_RIGHT"."CASE_ID" || ':' || "E_RIGHT"."ACTIVITY"
)

Input

Output

C_LEFT

CASE_ID : STRING

'CL1'

'CL2'

'CL3'

'CL4'

C_RIGHT

CASE_ID : STRING

'CR1'

'CR2'

'CR3'

'CR4'

C_R_MAP

CASE_ID_LEFT : STRING

CASE_ID_RIGHT : STRING

'CL1'

'CR1'

'CL1'

'CR3'

'CL2'

'CR2'

'CL2'

'CR4'

'CL3'

'CR1'

'CL3'

'CR3'

'CL4'

'CR2'

'CL4'

'CR4'

E_LEFT

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

LEFT_MSG : STRING

'CL1'

'A'

Sun Jan 01 2017 13:02:00.000

null

'CL1'

'B'

Sun Jan 01 2017 13:03:00.000

null

'CL2'

'C'

Sun Jan 01 2017 13:04:00.000

'N1'

'CL3'

'D'

Sun Jan 01 2017 13:00:00.000

null

'CL3'

'A'

Sun Jan 01 2017 13:01:00.000

'N3'

'CL4'

'B'

Sun Jan 01 2017 13:05:00.000

'N1'

'CL4'

'C'

Sun Jan 01 2017 13:08:00.000

null

E_RIGHT

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

RIGHT_MSG : STRING

'CR1'

'E'

Sun Jan 01 2017 13:00:00.000

null

'CR1'

'F'

Sun Jan 01 2017 13:01:00.000

null

'CR2'

'G'

Sun Jan 01 2017 13:05:00.000

'N1'

'CR3'

'H'

Sun Jan 01 2017 13:02:00.000

'N3'

'CR4'

'F'

Sun Jan 01 2017 13:06:00.000

'N1'

'CR4'

'G'

Sun Jan 01 2017 13:07:00.000

null

Foreign Keys

C_LEFT.CASE_ID

C_R_MAP.CASE_ID_LEFT

C_RIGHT.CASE_ID

C_R_MAP.CASE_ID_RIGHT

C_RIGHT.CASE_ID

E_RIGHT.CASE_ID

C_LEFT.CASE_ID

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

[4] TRANSIT_COLUMN calculates, based on the TIMESTAMP_INTERLEAVED_MINER, all transition edges between related cases in a m:n scenario. Therefore, the TIMESTAMP_INTERLEAVED_MINER calculates all transition edges based on the activity columns "E_LEFT"."ACTIVITY" and "E_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

"E_LEFT"."CASE_ID" || ':' || "E_LEFT"."ACTIVITY"

Column2

TRANSIT_COLUMN (
    TIMESTAMP_INTERLEAVED_MINER ( "E_LEFT"."ACTIVITY" , "E_RIGHT"."ACTIVITY" ) ,
    "E_RIGHT"."CASE_ID" || ':' || "E_RIGHT"."ACTIVITY"
)

Input

Output

C_LEFT

CASE_ID : STRING

'CL1'

'CL2'

'CL3'

'CL4'

C_RIGHT

CASE_ID : STRING

'CR1'

'CR2'

'CR3'

'CR4'

C_R_MAP

CASE_ID_LEFT : STRING

CASE_ID_RIGHT : STRING

'CL1'

'CR1'

'CL1'

'CR3'

'CL2'

'CR2'

'CL2'

'CR4'

'CL3'

'CR1'

'CL3'

'CR3'

'CL4'

'CR2'

'CL4'

'CR4'

E_LEFT

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

LEFT_MSG : STRING

'CL1'

'A'

Sun Jan 01 2017 13:02:00.000

null

'CL1'

'B'

Sun Jan 01 2017 13:03:00.000

null

'CL2'

'C'

Sun Jan 01 2017 13:04:00.000

'N1'

'CL3'

'D'

Sun Jan 01 2017 13:00:00.000

null

'CL3'

'A'

Sun Jan 01 2017 13:01:00.000

'N3'

'CL4'

'B'

Sun Jan 01 2017 13:05:00.000

'N1'

'CL4'

'C'

Sun Jan 01 2017 13:08:00.000

null

E_RIGHT

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

RIGHT_MSG : STRING

'CR1'

'E'

Sun Jan 01 2017 13:00:00.000

null

'CR1'

'F'

Sun Jan 01 2017 13:01:00.000

null

'CR2'

'G'

Sun Jan 01 2017 13:05:00.000

'N1'

'CR3'

'H'

Sun Jan 01 2017 13:02:00.000

'N3'

'CR4'

'F'

Sun Jan 01 2017 13:06:00.000

'N1'

'CR4'

'G'

Sun Jan 01 2017 13:07:00.000

null

Foreign Keys

C_LEFT.CASE_ID

C_R_MAP.CASE_ID_LEFT

C_RIGHT.CASE_ID

C_R_MAP.CASE_ID_RIGHT

C_RIGHT.CASE_ID

E_RIGHT.CASE_ID

C_LEFT.CASE_ID

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

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.

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

Ouput

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'

Sun Jan 01 2017 13:02:00.000

null

'CI1'

'B'

Sun Jan 01 2017 13:03:00.000

'PN1025'

'CI1'

'C'

Sun Jan 01 2017 13:04:00.000

null

'CI2'

'D'

Sun Jan 01 2017 13:00:00.000

null

'CI2'

'A'

Sun Jan 01 2017 13:01:00.000

null

'CI2'

'B'

Sun Jan 01 2017 13:05:00.000

'PN1025'

'CI2'

'C'

Sun Jan 01 2017 13:08:00.000

null

E_RN

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

OUT_MSG : STRING

'CI1a'

'E'

Sun Jan 01 2017 13:00:00.000

'PN1025'

'CI1a'

'F'

Sun Jan 01 2017 13:01:00.000

null

'CI1b'

'G'

Sun Jan 01 2017 13:05:00.000

null

'CI2a'

'H'

Sun Jan 01 2017 13:02:00.000

'PN1025'

'CI2b'

'F'

Sun Jan 01 2017 13:06:00.000

null

'CI2b'

'G'

Sun Jan 01 2017 13:07: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.

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

Sun Jan 01 2017 13:02:00.000

null

'CI1'

'B'

Sun Jan 01 2017 13:03:00.000

'PN1025'

'CI1'

'C'

Sun Jan 01 2017 13:04:00.000

null

'CI2'

'D'

Sun Jan 01 2017 13:00:00.000

null

'CI2'

'A'

Sun Jan 01 2017 13:01:00.000

null

'CI2'

'B'

Sun Jan 01 2017 13:05:00.000

'PN1025'

'CI2'

'C'

Sun Jan 01 2017 13:08:00.000

null

E_RN

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

OUT_MSG : STRING

'CI1a'

'E'

Sun Jan 01 2017 13:00:00.000

'PN1025'

'CI1a'

'F'

Sun Jan 01 2017 13:01:00.000

null

'CI1b'

'G'

Sun Jan 01 2017 13:05:00.000

null

'CI2a'

'H'

Sun Jan 01 2017 13:02:00.000

'PN1025'

'CI2b'

'F'

Sun Jan 01 2017 13:06:00.000

null

'CI2b'

'G'

Sun Jan 01 2017 13:07: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 m:n scenario. Therefore, the MATCH_MINER calculates the transition edges based on the activity columns "E_LEFT"."ACTIVITY" and "E_RIGHT"."ACTIVITY" as well as the in and out messages from "E_LEFT"."LEFT_MSG" and "E_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

"E_LEFT"."CASE_ID" || ':' || "E_LEFT"."ACTIVITY"

Column2

TRANSIT_COLUMN (
    MATCH_MINER (
        "E_LEFT"."ACTIVITY" ,
        "E_RIGHT"."ACTIVITY" ,
        "E_LEFT"."LEFT_MSG" ,
        "E_RIGHT"."RIGHT_MSG"
    ) ,
    "E_RIGHT"."CASE_ID" || ':' || "E_RIGHT"."ACTIVITY"
)

Input

Output

C_LEFT

CASE_ID : STRING

'CL1'

'CL2'

'CL3'

'CL4'

C_RIGHT

CASE_ID : STRING

'CR1'

'CR2'

'CR3'

'CR4'

C_R_MAP

CASE_ID_LEFT : STRING

CASE_ID_RIGHT : STRING

'CL1'

'CR1'

'CL1'

'CR3'

'CL2'

'CR2'

'CL2'

'CR4'

'CL3'

'CR1'

'CL3'

'CR3'

'CL4'

'CR2'

'CL4'

'CR4'

E_LEFT

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

LEFT_MSG : STRING

'CL1'

'A'

Sun Jan 01 2017 13:02:00.000

null

'CL1'

'B'

Sun Jan 01 2017 13:03:00.000

null

'CL2'

'C'

Sun Jan 01 2017 13:04:00.000

'N1'

'CL3'

'D'

Sun Jan 01 2017 13:00:00.000

null

'CL3'

'A'

Sun Jan 01 2017 13:01:00.000

'N3'

'CL4'

'B'

Sun Jan 01 2017 13:05:00.000

'N1'

'CL4'

'C'

Sun Jan 01 2017 13:08:00.000

null

E_RIGHT

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

RIGHT_MSG : STRING

'CR1'

'E'

Sun Jan 01 2017 13:00:00.000

null

'CR1'

'F'

Sun Jan 01 2017 13:01:00.000

null

'CR2'

'G'

Sun Jan 01 2017 13:05:00.000

'N1'

'CR3'

'H'

Sun Jan 01 2017 13:02:00.000

'N3'

'CR4'

'F'

Sun Jan 01 2017 13:06:00.000

'N1'

'CR4'

'G'

Sun Jan 01 2017 13:07:00.000

null

Foreign Keys

C_LEFT.CASE_ID

C_R_MAP.CASE_ID_LEFT

C_RIGHT.CASE_ID

C_R_MAP.CASE_ID_RIGHT

C_RIGHT.CASE_ID

E_RIGHT.CASE_ID

C_LEFT.CASE_ID

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

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

Sun Jan 01 2017 13:02:00.000

null

'CI1'

'B'

Sun Jan 01 2017 13:03:00.000

'PN1025'

'CI1'

'C'

Sun Jan 01 2017 13:04:00.000

null

'CI2'

'D'

Sun Jan 01 2017 13:00:00.000

null

'CI2'

'A'

Sun Jan 01 2017 13:01:00.000

null

'CI2'

'B'

Sun Jan 01 2017 13:05:00.000

'PN1025'

'CI2'

'C'

Sun Jan 01 2017 13:08:00.000

null

E_RN

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

OUT_MSG : STRING

'CI1a'

'E'

Sun Jan 01 2017 13:00:00.000

'PN1025'

'CI1a'

'F'

Sun Jan 01 2017 13:01:00.000

null

'CI1b'

'G'

Sun Jan 01 2017 13:05:00.000

null

'CI2a'

'H'

Sun Jan 01 2017 13:02:00.000

'PN1025'

'CI2b'

'F'

Sun Jan 01 2017 13:06:00.000

null

'CI2b'

'G'

Sun Jan 01 2017 13:07: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 m:n scenario. Therefore, the MANUAL_MINER calculates the transition edges based on the activity columns "E_RIGHT"."ACTIVITY" and "E_LEFT"."ACTIVITY" and the manually set combination ['F', 'B']. 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

"E_LEFT"."ACTIVITY"

Column2

TRANSIT_COLUMN (
    MANUAL_MINER ( "E_RIGHT"."ACTIVITY" , "E_LEFT"."ACTIVITY" , [ 'F' , 'B' ] ) ,
    "E_RIGHT"."ACTIVITY"
)

Input

Output

C_LEFT

CASE_ID : STRING

'CL1'

'CL2'

'CL3'

'CL4'

C_RIGHT

CASE_ID : STRING

'CR1'

'CR2'

'CR3'

'CR4'

C_R_MAP

CASE_ID_LEFT : STRING

CASE_ID_RIGHT : STRING

'CL1'

'CR1'

'CL1'

'CR3'

'CL2'

'CR2'

'CL2'

'CR4'

'CL3'

'CR1'

'CL3'

'CR3'

'CL4'

'CR2'

'CL4'

'CR4'

E_LEFT

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

LEFT_MSG : STRING

'CL1'

'A'

Sun Jan 01 2017 13:02:00.000

null

'CL1'

'B'

Sun Jan 01 2017 13:03:00.000

null

'CL2'

'C'

Sun Jan 01 2017 13:04:00.000

'N1'

'CL3'

'D'

Sun Jan 01 2017 13:00:00.000

null

'CL3'

'A'

Sun Jan 01 2017 13:01:00.000

'N3'

'CL4'

'B'

Sun Jan 01 2017 13:05:00.000

'N1'

'CL4'

'C'

Sun Jan 01 2017 13:08:00.000

null

E_RIGHT

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

RIGHT_MSG : STRING

'CR1'

'E'

Sun Jan 01 2017 13:00:00.000

null

'CR1'

'F'

Sun Jan 01 2017 13:01:00.000

null

'CR2'

'G'

Sun Jan 01 2017 13:05:00.000

'N1'

'CR3'

'H'

Sun Jan 01 2017 13:02:00.000

'N3'

'CR4'

'F'

Sun Jan 01 2017 13:06:00.000

'N1'

'CR4'

'G'

Sun Jan 01 2017 13:07:00.000

null

Foreign Keys

C_LEFT.CASE_ID

C_R_MAP.CASE_ID_LEFT

C_RIGHT.CASE_ID

C_R_MAP.CASE_ID_RIGHT

C_RIGHT.CASE_ID

E_RIGHT.CASE_ID

C_LEFT.CASE_ID

E_LEFT.CASE_ID

Result

Column1 : STRING

Column2 : STRING

'B'

'F'

'B'

'F'