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.
Column1
RANGE_APPEND ( ROUND_MONTH ( "Table"."DateColumn" ) , '1M' )
Column2
COUNT ( "Table"."DateColumn" )
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 |
Column1
RANGE_APPEND ( "Table"."IntColumn" , 1 )
Column2
"Table"."IntColumn"
Column1
RANGE_APPEND ( "HttpErrors"."StatusCode" , 1 , 400 , 410 )
Column2
COUNT ( "HttpErrors"."StatusCode" )
Column1
RANGE_APPEND ( "Table"."IntColumn" , 2 )
Column1
RANGE_APPEND ( ROUND_MONTH ( "Table"."DateColumn" ) , '1M' , {d '2019-01-01' } , {d '2019-12-01' } )
Column2
COUNT ( "Table"."DateColumn" )
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 |
Column1
RANGE_APPEND ( "Table"."DateColumn" , '1M' )
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 |
Column1
RANGE_APPEND ( ROUND_WEEK ( "Table"."DateColumn" ) , '7D' )
Column2
COUNT ( "Table"."DateColumn" )
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 |
Column1
RANGE_APPEND ( "Table"."DateColumn" , '1h' )
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 |