Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

MATCH_ACTIVITIES

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

Description

MATCH_ACTIVITIES flags cases with certain activities without taking the exact order of activities into account. If the order is of importance, use MATCH_PROCESS_REGEX or MATCH_PROCESS.

Syntax
MATCH_ACTIVITIES([  activity_column , ] [STARTING  activity_list  ] [, ] [NODE  activity_list  ] [, ] [NODE_ANY  activity_list  ] [, ] [ENDING  activity_list  ] [, ] [EXCLUDING  activity_list  ] [, ] [EXCLUDING_ALL  activity_list  ])

  • activity_column: string column of the activity table

  • activity_list: [ activity_name [, activity_name ]* ]

  • STARTING: case has to start with specified activity

  • NODE: case has to have the specified activities

  • NODE_ANY: case has to have at least one of the specified activities

  • ENDING: case has to end with specified activity

  • EXCLUDING: case must not have the specified activities

  • EXCLUDING_ALL: case must not have any of the specified activities

NULL handling

NULL values do not influence the case flag and are ignored.

[1] In this example STARTING and ENDING are combined to flag cases which begin with activity 'A' and end with activity 'B'. The rows with a null value are ignored.

Query

Filter

FILTER MATCH_ACTIVITIES ( "Table1"."ACTIVITY" , STARTING [ 'A' ] , ENDING [ 'B' ] ) = 1;

Column1

"Table1"."CASE_ID"

Column2

"Table1"."ACTIVITY"

Input

Output

Table1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

null

Tue Jan 01 2019 13:00:00.000

'1'

'A'

Tue Jan 01 2019 13:01:00.000

'1'

null

Tue Jan 01 2019 13:02:00.000

'1'

null

Tue Jan 01 2019 13:03:00.000

'1'

'B'

Tue Jan 01 2019 13:04:00.000

'2'

'C'

Tue Jan 01 2019 13:02:00.000

'2'

null

Tue Jan 01 2019 13:03:00.000

'3'

null

Tue Jan 01 2019 13:00:00.000

'3'

'B'

Tue Jan 01 2019 13:01:00.000

Result

Column1 : STRING

Column2 : STRING

'1'

null

'1'

'A'

'1'

null

'1'

null

'1'

'B'

Result

MATCH_ACTIVITIES returns an integer column, which flags all matching cases with 1 and all non matching cases with a 0. The result is often used in combination with a filter.

Combine specifiers

If you are combining several specifiers, keep in mind that the order of the specifiers matters. For example START has to come before NODE.

Examples

[2] Here MATCH_ACTIVITIES is used to filter cases which have activity 'A' and 'B'. The order doesn't matter but both activities have to be present in a case.

Query

Filter

FILTER MATCH_ACTIVITIES ( "Table1"."ACTIVITY" , NODE [ 'B' , 'A' ] ) = 1;

Column1

"Table1"."CASE_ID"

Column2

"Table1"."ACTIVITY"

Input

Output

Table1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'2'

'C'

Tue Jan 01 2019 13:02:00.000

'2'

'D'

Tue Jan 01 2019 13:03:00.000

Output

Column1 : STRING

Column2 : STRING

'1'

'A'

'1'

'B'

[3] In this example MATCH_ACTIVITIES, by using the keyword NODE_ANY, flags all cases which have either activity 'B', 'C' or both.

Query

Filter

FILTER MATCH_ACTIVITIES ( "Table1"."ACTIVITY" , NODE_ANY [ 'B' , 'C' ] ) = 1;

Column1

"Table1"."CASE_ID"

Column2

"Table1"."ACTIVITY"

Input

Output

Table1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'2'

'C'

Tue Jan 01 2019 13:02:00.000

'2'

'D'

Tue Jan 01 2019 13:03:00.000

Result

Column1 : STRING

Column2 : STRING

'1'

'A'

'1'

'B'

'2'

'C'

'2'

'D'

[4] The keyword EXCLUDING excludes all cases which have activity 'B', 'C' or both.

Query

Filter

FILTER MATCH_ACTIVITIES ( "Table1"."ACTIVITY" , EXCLUDING [ 'B' , 'C' ] ) = 1;

Column1

"Table1"."CASE_ID"

Column2

"Table1"."ACTIVITY"

Input

Output

Table1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'2'

'C'

Tue Jan 01 2019 13:02:00.000

'2'

'D'

Tue Jan 01 2019 13:03:00.000

Result

Column1 : STRING

Column2 : STRING

[5] In this example, applying EXCLUDING_ALL excludes all cases which have activity 'A' and 'B'.

Query

Filter

FILTER MATCH_ACTIVITIES ( "Table1"."ACTIVITY" , EXCLUDING_ALL [ 'B' , 'A' ] ) = 1;

Column1

"Table1"."CASE_ID"

Column2

"Table1"."ACTIVITY"

Input

Output

Table1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'2'

'C'

Tue Jan 01 2019 13:02:00.000

'2'

'D'

Tue Jan 01 2019 13:03:00.000

Result

Column1 : STRING

Column2 : STRING

'2'

'C'

'2'

'D'

[6] In this example STARTING and ENDING are combined to flag cases which begin with activity 'A' and end with activity 'B'.

Query

Filter

FILTER MATCH_ACTIVITIES ( "Table1"."ACTIVITY" , STARTING [ 'A' ] , ENDING [ 'B' ] ) = 1;

Column1

"Table1"."CASE_ID"

Column2

"Table1"."ACTIVITY"

Input

Output

Table1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'2'

'C'

Tue Jan 01 2019 13:02:00.000

'2'

'D'

Tue Jan 01 2019 13:03:00.000

Result

Column1 : STRING

Column2 : STRING

'1'

'A'

'1'

'B'

See also: