Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

VARIANT

Applies to: CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6

Description

Variant aggregates all activities of a case into a string, which represents a variant of the process.

Syntax
VARIANT ( table.column )

  • column: String column which has to be joinable to the activity table.

SHORTENED ( VARIANT ( table.column ) [, max_cycle_length ] )

  • column: String column which has to be joinable to the activity table.

  • max_cycle_length: More than max_cycle_length consecutive occurrences of the same activity are reduced to max_cycle_length occurrences (Since: CELONIS 4.6). Default Value is 2.

 
Input column

The input column doesn't have to be necessary an activity column. Other string columns are also possible. This way it is also possible to create variants based on a resource column. Or activities can be grouped together by mapping activity names e.g. with REMAP_VALUES. VARIANT ignores NULL values.

Examples

[1] This example shows how VARIANT takes all activities per case and creates variants out of it.

Query

Column1

"Activities_CASES"."CASE_ID"

Column2

VARIANT ( "Activities"."ACTIVITY" )

Input

Output

Activities

CASE_ID : INT

ACTIVITY : STRING

TIMESTAMP : DATE

1

'A'

Tue Jan 01 2019 13:00:00.000

1

'B'

Tue Jan 01 2019 13:01:00.000

1

'C'

Tue Jan 01 2019 13:02:00.000

2

'A'

Tue Jan 01 2019 13:03:00.000

2

'B'

Tue Jan 01 2019 13:04:00.000

2

'B'

Tue Jan 01 2019 13:05:00.000

2

'C'

Tue Jan 01 2019 13:06:00.000

3

'A'

Tue Jan 01 2019 13:07:00.000

3

'B'

Tue Jan 01 2019 13:08:00.000

3

'B'

Tue Jan 01 2019 13:09:00.000

3

'B'

Tue Jan 01 2019 13:09:00.000

3

'C'

Tue Jan 01 2019 13:11:00.000

Activities_CASES

CASE_ID : INT

1

2

3

Foreign Keys

Activities.CASE_ID

Cases.CASE_ID

Result

Column1 : INT

Column2 : STRING

1

'A, B, C'

2

'A, B, B, C'

3

'A, B, B, B, C'

[2] If SHORTENED is applied to VARIANT, the loop in case three reduces to a simple loop.

Query

Column1

"Activities_CASES"."CASE_ID"

Column2

SHORTENED ( VARIANT ( "Activities"."ACTIVITY" ) )

Input

Output

Activities

CASE_ID : INT

ACTIVITY : STRING

TIMESTAMP : DATE

1

'A'

Tue Jan 01 2019 13:00:00.000

1

'B'

Tue Jan 01 2019 13:01:00.000

1

'C'

Tue Jan 01 2019 13:02:00.000

2

'A'

Tue Jan 01 2019 13:03:00.000

2

'B'

Tue Jan 01 2019 13:04:00.000

2

'B'

Tue Jan 01 2019 13:05:00.000

2

'C'

Tue Jan 01 2019 13:06:00.000

3

'A'

Tue Jan 01 2019 13:07:00.000

3

'B'

Tue Jan 01 2019 13:08:00.000

3

'B'

Tue Jan 01 2019 13:09:00.000

3

'B'

Tue Jan 01 2019 13:10:00.000

3

'C'

Tue Jan 01 2019 13:11:00.000

Activities_CASES

CASE_ID : INT

1

2

3

Foreign Keys

Cases.CASE_ID

Result

Column1 : INT

Column2 : STRING

1

'A, B, C'

2

'A, B, B, C'

3

'A, B, B, C'

[3] Here is an example in which, instead of the ACTIVITY column, another column is used as input for VARIANT.

Query

Column1

"Activities_CASES"."CASE_ID"

Column2

VARIANT ( "Activities"."COUNTRY" )

Input

Output

Activities

CASE_ID : INT

ACTIVITY : STRING

TIMESTAMP : DATE

COUNTRY : STRING

1

'A'

Tue Jan 01 2019 13:00:00.000

'US'

1

'B'

Tue Jan 01 2019 13:01:00.000

'GB'

1

'C'

Tue Jan 01 2019 13:02:00.000

'GB'

2

'A'

Tue Jan 01 2019 13:03:00.000

'FR'

2

'B'

Tue Jan 01 2019 13:04:00.000

'AT'

2

'B'

Tue Jan 01 2019 13:05:00.000

'DE'

Activities_CASES

CASE_ID : INT

1

2

Foreign Keys

Activities.CASE_ID

Cases.CASE_ID

Result

Column1 : INT

Column1 : INT

1

'US, GB, GB'

2

'FR, AT, DE'

[4] Here activity B and C grouped together to BC using REMAP_VALUES.

Query

Column1

"Activities_CASES"."CASE_ID"

Column2

VARIANT ( REMAP_VALUES ( "Activities"."ACTIVITY", ['B', 'BC'], ['C', 'BC'] ) )

Input

Output

Activities

CASE_ID : INT

ACTIVITY : STRING

TIMESTAMP : DATE

1

'A'

Tue Jan 01 2019 13:00:00.000

1

'B'

Tue Jan 01 2019 13:01:00.000

1

'C'

Tue Jan 01 2019 13:02:00.000

2

'A'

Tue Jan 01 2019 13:03:00.000

2

'C'

Tue Jan 01 2019 13:04:00.000

2

'B'

Tue Jan 01 2019 13:05:00.000

Activities_CASES

CASE_ID : INT

1

2

Foreign Keys

Activities.CASE_ID

Cases.CASE_ID

Result

Column1 : INT

Column2 : STRING

1

'A, BC, BC'

2

'A, BC, BC'

[5] Null values are ignored by VARIANT.

Query

Column1

"Activities_CASES"."CASE_ID""Cases"."CASE_ID"

Column2

VARIANT ( "Activities"."ACTIVITY" )

Input

Output

Activities

CASE_ID : INT

ACTIVITY : STRING

TIMESTAMP : DATE

1

'A'

Tue Jan 01 2019 13:00:00.000

1

null

Tue Jan 01 2019 13:01:00.000

1

'C'

Tue Jan 01 2019 13:02:00.000

2

'A'

Tue Jan 01 2019 13:03:00.000

2

'C'

Tue Jan 01 2019 13:04:00.000

2

null

Tue Jan 01 2019 13:05:00.000

Activities_CASES

CASE_ID : INT

1

2

Foreign Key

Activities.CASE_ID

Cases.CASE_ID

Result

Column1 : INT

Column2 : STRING

1

'A, C'

2

'A, C'