Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

CALC_CROP_TO_NULL

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

Description

This operator crops cases to a range of activities. All values outside this range are mapped to null. Values within the range are kept, in contrast to CALC_CROP which maps them to 1.

Syntax
CALC_CROP_TO_NULL ( begin_range_specifier TO end_range_specifier, table.column )

  • begin_range_specifier: CASE_START | FIRST_OCCURRENCE [ activity_name ] | LAST_OCCURRENCE [ activity_name ]

  • end_range_specifier: CASE_END | FIRST_OCCURRENCE [ activity_name ] | LAST_OCCURRENCE [ activity_name ]

  • column: A string column of an activity table. Usually, the activity column of an activity table is used.

Range Specifier

  • FIRST_OCCURRENCE['activity_name']: First occurrence of the specified activity type.

  • LAST_OCCURRENCE['activity_name']: Last occurrence of the specified activity type.

  • CASE_START: Range begins at the start of the case.

  • CASE_END: Range ends at the end of the case.

Examples

[1] Simple example with one matching (in case 1 B comes before C) and one not matching (in case 2 there is no C after B) case.

Query

Column1 

         CALC_CROP_TO_NULL ( FIRST_OCCURRENCE [ 'B' ] TO LAST_OCCURRENCE [ 'C' ] , "Table1"."activity" )

Input

Output

Table1

case : INT

activity : STRING

timestamp : DATE

1

'A'

Mon Feb 01 2016 01:00:00.000

1

'B'

Mon Feb 01 2016 02:00:00.000

1

'C'

Mon Feb 01 2016 03:00:00.000

1

'D'

Mon Feb 01 2016 04:00:00.000

2

'A'

Mon Feb 01 2016 01:00:00.000

2

'B'

Mon Feb 01 2016 02:00:00.000

2

'D'

Mon Feb 01 2016 03:00:00.000

2

'E'

Mon Feb 01 2016 04:00:00.000

Result

Column1 : STRING

null

'B'

'C'

null

null

null

null

null

[2] If range specifiers are conflicting, e.g. end activity is before start activity the complete case is mapped to null.

Query

Column1 

         CALC_CROP_TO_NULL ( FIRST_OCCURRENCE [ 'C' ] TO LAST_OCCURRENCE [ 'B' ] , "Table1"."activity" )

Input

Output

Table1

case : INT

activity : STRING

timestamp : DATE

1

'A'

Mon Feb 01 2016 01:00:00.000

1

'B'

Mon Feb 01 2016 02:00:00.000

1

'C'

Mon Feb 01 2016 03:00:00.000

1

'D'

Mon Feb 01 2016 04:00:00.000

Result

Column1 : STRING

null

null

null

null

[3] Example for FIRST_OCCURRENCE and LAST_OCCURRENCE if activities appear multiple times.

Query

Column1 

         CALC_CROP_TO_NULL ( LAST_OCCURRENCE [ 'A' ] TO FIRST_OCCURRENCE [ 'B' ] , "Table1"."activity" )

Input

Output

Table1

case : INT

activity : STRING

timestamp : DATE

1

'A'

Mon Feb 01 2016 01:00:00.000

1

'A'

Mon Feb 01 2016 02:00:00.000

1

'B'

Mon Feb 01 2016 03:00:00.000

1

'B'

Mon Feb 01 2016 04:00:00.000

Result

Column1 : STRING

null

'A'

'B'

null

[4] Example how CASE_START and CASE_END can be applied. They can also be mixed LAST_OCCURRENCE and FIRST_OCCURRENCE.

Query

Column1 

         CALC_CROP_TO_NULL ( CASE_START TO CASE_END , "Table1"."activity" )

Input

Output

Table1

case : INT

activity : STRING

timestamp : DATE

1

'A'

Mon Feb 01 2016 01:00:00.000

1

'A'

Mon Feb 01 2016 02:00:00.000

1

'B'

Mon Feb 01 2016 03:00:00.000

1

'B'

Mon Feb 01 2016 04:00:00.000

Result

Column1 : STRING

'A'

'A'

'B'

'B'

[5] Example how through using REMAP_VALUES, CALC_CROP_TO_NULL can be used to crop to the last occurrence of B or C.

Query

Column1 

         CALC_CROP_TO_NULL ( FIRST_OCCURRENCE [ 'A' ] TO LAST_OCCURRENCE [ 'Z' ] , REMAP_VALUES ( "Table1"."activity" , [ 'B' , 'Z' ] , [ 'C' , 'Z' ] ) )

Input

Output

Table1

case : INT

activity : STRING

timestamp : DATE

1

'A'

Mon Feb 01 2016 01:00:00.000

1

'B'

Mon Feb 01 2016 02:00:00.000

1

'C'

Mon Feb 01 2016 03:00:00.000

1

'D'

Mon Feb 01 2016 04:00:00.000

Result

Column1 : STRING

'A'

'Z'

'Z'

null