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:
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:
|
[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: |
[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: |
[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): |
[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:
|
[6] Simple example where the range is appended to a DATE column, with a step size of 1 month:
|
[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:
|
[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:
|