Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

RANGE_APPEND

Applies to: CELONIS 4.7

Description

RANGE_APPEND creates values inside a range based on a given step size and appends them to the given column.

RANGE_APPEND can be used with INT and DATE columns.

The function generates a temporary table which is joined to the input column. The column in the generated table contains a copy of the input column with all values inside the specified range appended. The range can be specified by passing a step size, a start and an end value to RANGE_APPEND. Start and end values are optional; if they are not set, the minimum and maximum values of the input column are used as range bounds.

The start value of the range is always appended to the column, as well as all values based on the start value and the given step size that are less than or equal to the end value of the range.

The RANGE_APPEND functionality can be used if values inside a range (e.g. DATE range) are not present in the data, but should still be shown when aggregating data for that range.

The maximum number of generated elements is limited to 10,000. If this limit is exceeded, an error is returned.

Syntax
RANGE_APPEND ( table.column, step_size [, range_start [, range_end ] ] )
  • table.column: The input column to which the generated values should be appended. Constants are not allowed.

  • step_size: The size of each interval. The type of the step_size depends on the data type of the input column:

    • INT input column: The step_size must be an INT.

    • DATE input column: The step_size is described as a STRING constant in the following form:

      '<number><unit>' (e.g. '1M'), where <number> is the number of units and <unit> is one of:

      • h: hour

      • D: day

      • M: month

      • Y: year

  • range_start: The value where the generated value range should start from. It must be of the same data type as the input column. If this is not set, the smallest value of the input column is used.

  • range_end: The value where the generated value range should end. It must be of the same data type as the input column. If this is not set, the largest value of the input column is used.

Functions which take FILTERs into account (like aggregation functions) are not allowed as any input parameter.

[1] In this example, we want to count the number of values per month, including the months that are not contained in the input column. To do this, we use ROUND_MONTH to round every date to the month first, and then apply RANGE_APPEND with a step size of one month as a dimension. We aggregate using the COUNT function:

Query

Column1

RANGE_APPEND ( ROUND_MONTH ( "Table"."DateColumn" ) , '1M' )

Column2

COUNT ( "Table"."DateColumn" )

Input

Output

Table

DateColumn : DATE

Tue Jan 01 2019 00:00:00.000

Tue Feb 05 2019 00:00:00.000

Mon May 06 2019 00:00:00.000

Sat Jun 01 2019 00:00:00.000

Sun Jun 02 2019 00:00:00.000

Mon Jun 10 2019 00:00:00.000

Result

Column1 : DATE

Column2 : INT

Tue Jan 01 2019 00:00:00.000

1

Fri Feb 01 2019 00:00:00.000

1

Fri Mar 01 2019 00:00:00.000

0

Mon Apr 01 2019 00:00:00.000

0

Wed May 01 2019 00:00:00.000

1

Sat Jun 01 2019 00:00:00.000

3

[2] Simple example of RANGE_APPEND on an INT column with step size 1. The first four values of the result are equal to the four values of the input column, and they are joined to the corresponding input values. The complete range between the smallest and largest value of the input (1 and 4) is appended. The appended values are not joined to any value from the input column:

Query

Column1

RANGE_APPEND ( "Table"."IntColumn" , 1 )

Column2

"Table"."IntColumn"

Input

Output

Table

IntColumn : INT

1

2

4

4

Result

Column1 : INT

Column2 : INT

1

1

2

2

4

4

4

4

1

null

2

null

3

null

4

null

[3] In this example, we want to know for each HTTP status code between 400 and 410 how often this status occurs in the data. For this, we append the range between 400 and 410 with step size 1 and count the number of occurrences with a COUNT:

Query

Column1

RANGE_APPEND ( "HttpErrors"."StatusCode" , 1 , 400 , 410 )

Column2

COUNT ( "HttpErrors"."StatusCode" )

Input

Output

HttpErrors

Case : INT

StatusCode : INT

1

404

2

401

3

400

4

404

5

408

6

401

Result

Column1 : INT

Column2 : INT

400

1

401

2

402

0

403

0

404

2

405

0

406

0

407

0

408

1

409

0

410

0

[4] In this example, the step size is set to 2. Two values are appended: 1 (which is the start of the range because it is the smallest value in the input column) and 3. The next value in the range would have been 5, but this is already larger than the end of the range (which is 4 because 4 is the largest value in the input column):

Query

Column1

RANGE_APPEND ( "Table"."IntColumn" , 2 )

Input

Output

Table

IntColumn : INT

1

2

4

4

Result

Column1 : INT

1

2

4

4

1

3

[5] In this example, we want to count the number of values for each month of the year 2019, including the months that are not contained in the input column. We can pass the start and end bounds as dates, using the syntax described in the DATE documentation:

Query

Column1

RANGE_APPEND ( ROUND_MONTH ( "Table"."DateColumn" ) , '1M' , {d '2019-01-01' } , {d '2019-12-01' } )

Column2

COUNT ( "Table"."DateColumn" )

Input

Output

Table

DateColumn : DATE

Tue Feb 05 2019 00:00:00.000

Mon May 06 2019 00:00:00.000

Sat Jun 01 2019 00:00:00.000

Sun Jun 02 2019 00:00:00.000

Thu Oct 10 2019 00:00:00.000

Result

Column1 : DATE

Column2 : INT

Tue Jan 01 2019 00:00:00.000

0

Fri Feb 01 2019 00:00:00.000

1

Fri Mar 01 2019 00:00:00.000

0

Mon Apr 01 2019 00:00:00.000

0

Wed May 01 2019 00:00:00.000

1

Sat Jun 01 2019 00:00:00.000

2

Mon Jul 01 2019 00:00:00.000

0

Thu Aug 01 2019 00:00:00.000

0

Sun Sep 01 2019 00:00:00.000

0

Tue Oct 01 2019 00:00:00.000

1

Fri Nov 01 2019 00:00:00.000

0

Sun Dec 01 2019 00:00:00.000

0

[6] Simple example where the range is appended to a DATE column, with a step size of 1 month:

Query

Column1

RANGE_APPEND ( "Table"."DateColumn" , '1M' )

Input

Output

Table

DateColumn : DATE

Tue Jan 01 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Wed May 01 2019 00:00:00.000

Sat Jun 01 2019 00:00:00.000

Result

Column1 : DATE

Tue Jan 01 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Wed May 01 2019 00:00:00.000

Sat Jun 01 2019 00:00:00.000

Tue Jan 01 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Fri Mar 01 2019 00:00:00.000

Mon Apr 01 2019 00:00:00.000

Wed May 01 2019 00:00:00.000

Sat Jun 01 2019 00:00:00.000

[7] In this example, we want to count the number of values per week, including the weeks that are not contained in the input column. To do this, we use ROUND_WEEK to round every date to the week first, and then apply RANGE_APPEND with a step size of seven days (= one week) as a dimension. We aggregate using the COUNT function:

Query

Column1

RANGE_APPEND ( ROUND_WEEK ( "Table"."DateColumn" ) , '7D' )

Column2

COUNT ( "Table"."DateColumn" )

Input

Output

Table

DateColumn : DATE

Tue Jan 07 2020 00:00:00.000

Mon Jan 13 2020 00:00:00.000

Tue Jan 21 2020 00:00:00.000

Sat Jan 25 2020 00:00:00.000

Wed Feb 19 2020 00:00:00.000

Result

Column1 : DATE

Column2 : INT

Mon Jan 06 2020 00:00:00.000

1

Mon Jan 13 2020 00:00:00.000

1

Mon Jan 20 2020 00:00:00.000

2

Mon Jan 27 2020 00:00:00.000

0

Mon Feb 03 2020 00:00:00.000

0

Mon Feb 10 2020 00:00:00.000

0

Mon Feb 17 2020 00:00:00.000

1

[8] Example where the step size is set to one hour. The appended values are generated based on that step size starting with the first value of the range:

Query

Column1

RANGE_APPEND ( "Table"."DateColumn" , '1h' )

Input

Output

Table

DateColumn : DATE

Tue Jan 01 2019 10:30:00.000

Tue Jan 01 2019 11:00:00.000

Tue Jan 01 2019 13:00:00.000

Tue Jan 01 2019 16:00:00.000

Result

Column1 : DATE

Tue Jan 01 2019 10:30:00.000

Tue Jan 01 2019 11:00:00.000

Tue Jan 01 2019 13:00:00.000

Tue Jan 01 2019 16:00:00.000

Tue Jan 01 2019 10:30:00.000

Tue Jan 01 2019 11:30:00.000

Tue Jan 01 2019 12:30:00.000

Tue Jan 01 2019 13:30:00.000

Tue Jan 01 2019 14:30:00.000

Tue Jan 01 2019 15:30:00.000