Skip to main content

Celonis Product Documentation

MATCH_ACTIVITIES
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_table.string_column, ] [STARTING activity_list ] [, ] [NODE activity_list ] [, ] [NODE_ANY activity_list ] [, ] [ENDING activity_list ] [, ] [EXCLUDING activity_list ] [, ] [EXCLUDING_ALL activity_list ])

  • activity_table.string_column: A string column of an activity table. By default, the activity column of the default activity table is used.

  • activity_list: [ activity_name [, activity_name ]* ]

  • STARTING: case must start with one of the specified activities

  • NODE: case must have all of the specified activities

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

  • ENDING: case must end with one of the specified activities

  • EXCLUDING: case must not have any of the specified activities (and must have at least one non-NULL activity)

  • EXCLUDING_ALL: case must not have all of the specified activities (and must have at least one non-NULL activity)

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.

If a specifier contains an activity that doesn't exist, it's as if the activity exists but isn't found in any case. In this situation, a warning is issued.

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'

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

Result

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

(empty table)

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

[7]

Cases which are empty, meaning all their activities are null or no activities exist, are flagged as non-matching by returning 0. That also applies for the EXCLUDING keyword.

Query

Filter 

         FILTER MATCH_ACTIVITIES ( "ActivityTable"."ACTIVITY" , EXCLUDING [ 'A' ] ) = 1;

Column1 

         "CaseTable"."CASE_ID"

Input

Output

ActivityTable

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Thu Dec 01 2016 13:00:00.000

'2'

'B'

Thu Dec 01 2016 13:02:00.000

'3'

null

Tue Jan 01 2019 13:02:00.000

CaseTable

CASE_ID : string

'1'

'2'

'3'

'4'

Foreign Keys

CaseTable.CASE_ID

ActivityTable.CASE_ID

Result

Column1 : string

'2'

See also: