Skip to main content

CALC_CROP_TO_NULL

Description

This operator crops cases to a specified event range. All values outside this event range are mapped to null. Values within the specified event range are kept, in contrast to CALC_CROP which maps them to 1. The result is a temporary STRING column of the activity table. It contains activities of the specified range and null otherwise.

Syntax

CALC_CROP_TO_NULL ( begin_event_specifier TO end_event_specifier, activity_table.string_column )

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

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

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

Event Specifier

Specify where the event range shall start or end:

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

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

  • CASE_START: Event range begins at the start of the case.

  • CASE_END: Event range ends at the end of the case.

NULL handling

NULL values outside of ranges are ignored and will return NULL again. However, if a row within a range is NULL, then for this row NULL is returned.

[1]

CALC_CROP_TO_NULL query with null values within the range as well as outside of the range.

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

null

Mon Feb 01 2016 01:00:00.000

1

'B'

Mon Feb 01 2016 02:00:00.000

1

null

Mon Feb 01 2016 02:05:00.000

1

'C'

Mon Feb 01 2016 03:00:00.000

1

null

Mon Feb 01 2016 04:00:00.000

1

'C'

Mon Feb 01 2016 04:00:00.000

1

'L'

Mon Feb 01 2016 04:00:00.000

1

'C'

Mon Feb 01 2016 05:00:00.000

1

'D'

Mon Feb 01 2016 05:00:00.000

1

null

Mon Feb 01 2016 06:00:00.000

Result

Column1 : string

null

'B'

null

'C'

null

'C'

'L'

'C'

null

null

Examples

[2]

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

[3]

If event 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

null

null

null

null

[4]

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

[5]

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'

[6]

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

See also: