Skip to main content

Celonis Product Documentation

CALC_REWORK
Description

CALC_REWORK counts the number of activities per case. A filter expression can be specified to restrict the activities that should be taken into account. The result is an INT column in which the value of each row gives the number of activities for the respective case ID. The resulting column is temporarily added to the case table.

Syntax
  CALC_REWORK ()
 
  CALC_REWORK ( filter_condition )
 
  CALC_REWORK ( activity_table.column )
 
  CALC_REWORK ( filter_condition , activity_table.column )

  • filter_condition: All activities that match the filter condition will be counted.

  • activity_table.column: A column of an activity table. This can be used to specify the event log that should be used if multiple event logs are contained in the data model.

NULL handling

NULL values are in general not relevant for the calculation since CALC_REWORK counts how often a CASE_ID appears. However, if a case does not have a join partner in the case table, it will be ignored. If there is a NULL case id in the case table, it will be taken over into the result table. However, the result for it will be 0 since NULL cases are not joined.

[1]

CALC_REWORK with NULL values in the activity table. The first element in the result table represents the NULL case. However, NULL values in the activity table are ignored. Therefore, the result is 0:

Query

Column1 

         CALC_REWORK ( )

Input

Output

ACTIVITY_TABLE

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

null

Tue Jan 01 2019 13:00:00.000

null

'A'

Tue Jan 01 2019 13:00:00.000

null

'A'

Tue Jan 01 2019 13:04:00.000

'2'

'B'

Tue Jan 01 2019 13:01:00.000

'2'

null

Tue Jan 01 2019 13:02:00.000

'2'

'D'

Tue Jan 01 2019 13:03:00.000

'3'

'A'

Tue Jan 01 2019 13:04:00.000

'3'

null

Tue Jan 01 2019 13:05:00.000

null

'B'

Tue Jan 01 2019 13:06:00.000

CASE_TABLE

CASE_ID : string

null

'1'

'2'

'3'

Foreign Keys

CASE_TABLE.CASE_ID

ACTIVITY_TABLE.CASE_ID

Result

Column1 : int

0

1

3

2

Examples

[2]

Example for CALC_REWORK on 3 cases without filter:

Query

Column1 

         CALC_REWORK ( )

Input

Output

Activities

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'2'

'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

'3'

'A'

Tue Jan 01 2019 13:04:00.000

'3'

'A'

Tue Jan 01 2019 13:05:00.000

Result

Column1 : int

1

3

2

[3]

Example for CALC_REWORK on 3 cases without filter. The activity column that should be used is passed as a parameter. This can be used to specify the event log that should be used if multiple event logs are contained in the data model:

Query

Column1 

         CALC_REWORK ( "Activities"."ACTIVITY" )

Input

Output

Activities

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'2'

'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

'3'

'A'

Tue Jan 01 2019 13:04:00.000

'3'

'A'

Tue Jan 01 2019 13:05:00.000

Result

Column1 : int

1

3

2

[4]

Example for CALC_REWORK with filter on activities B and C:

Query

Column1 

         CALC_REWORK ( "Activities".ACTIVITY IN ( 'B' , 'C' ) )

Input

Output

Activities

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'2'

'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

'3'

'A'

Tue Jan 01 2019 13:04:00.000

'3'

'A'

Tue Jan 01 2019 13:05:00.000

Result

Column1 : int

0

2

0

[5]

Example for CALC_REWORK with filter on activities B and C with specified activity column. This can be used to specify the event log that should be used if multiple event logs are contained in the data model:

Query

Column1 

         CALC_REWORK ( "Activities".ACTIVITY IN ( 'B' , 'C' ) , "Activities".ACTIVITY )

Input

Output

Activities

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'2'

'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

'3'

'A'

Tue Jan 01 2019 13:04:00.000

'3'

'A'

Tue Jan 01 2019 13:05:00.000

Result

Column1 : int

0

2

0