Skip to main content

Celonis Product Documentation

GENERATE_RANGE
Description

GENERATE_RANGE creates values inside a range based on a given step size. It can be used to generate values for dropdown components.

GENERATE_RANGE can be used with INT and DATE columns.

The function generates a temporary table with the generated range as single column. The result table is not joined to any other table. The range is specified by passing a step size, a start and an end value to GENERATE_RANGE. The start and end values must have the same data-type, the generated range is on the interval [start, end].

The maximum number of generated elements is limited to 10,000. If this limit is exceeded, an error is returned. The step size must be positive and range_end must be greater or equal than range_start

Syntax
 GENERATE_RANGE ( step_size , range_start , range_end )
  • step_size: The size of each interval. The type of the step_size depends on the data type of the range_start:

    • INT range_start: The step_size must be an INT.

    • DATE range_start: 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 type INT or DATE.

  • range_end: The value where the generated value range should end. It must be of the same data type as the range_start.

Use Cases
  • The result of GENERATE_RANGE can be used to generate values for a dropdown component.

  • To create a unique index column for a certain table, UNIQUE_ID or INDEX_ORDER can be used instead.

Examples

[1]

Simple example where GENERATE_RANGE is used to create an INT column with values between 1 and 4 and step size 1

Query

Column1

         GENERATE_RANGE ( 1 , 1 , 4 )
        

Input

Output

Result

Column1 : int

1

2

3

4

[2]

Calculated expressions are also allowed for the step, start, and end values, as long as the calculated value does not depend on the filter state. In this example, the range is set to go from the minimum to the maximum value of IntColumn

Query

Column1

         GENERATE_RANGE ( 2 , PU_MIN ( CONSTANT ( ) , "Table"."IntColumn" ) , PU_MAX ( CONSTANT ( ) , "Table"."IntColumn" ) )
        

Input

Output

Table

IntColumn : int

2

1

4

10

Result

Column1 : int

1

3

5

7

9

[3]

This example shows the usage of GENERATE_RANGE with negative start and end operators. Recall that the step size still must be positive

Query

Column1

         GENERATE_RANGE ( 2 , - 10 , - 1 )
        

Input

Output

Result

Column1 : int

-10

-8

-6

-4

-2

[4]

Simple example of how to use GENERATE_RANGE to generate DATE range, with a step size of 1 month.

Query

Column1

         GENERATE_RANGE ( '1M' , {d '2019-01-01' } , {d '2019-06-01' } )
        

Input

Output

Result

Column1 : date

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

[5]

Example where the step size is set to one hour. The minutes are also considered for the range boundaries

Query

Column1

         GENERATE_RANGE ( '1h' , {d '2019-06-01 18:30:00' } , {d '2019-06-01 23:00:00' } )
        

Input

Output

Result

Column1 : date

Sat Jun 01 2019 18:30:00.000

Sat Jun 01 2019 19:30:00.000

Sat Jun 01 2019 20:30:00.000

Sat Jun 01 2019 21:30:00.000

Sat Jun 01 2019 22:30:00.000

See also: