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 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 (and must have at least one non-NULL activity)

  • EXCLUDING_ALL: case must not have any 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.

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: