Skip to main content
This gem runs in .

​
Overview

The GenerateRows gem creates new rows of data at the record level using iterative expressions. This gem generates sequences of numbers, dates, or other values based on initialization, condition, and loop expressions. The gem follows a three-step process to create rows:
  1. Initialization Expression: Sets the starting value for the first row.
  2. Condition Expression: Defines when to stop generating rows (true/false condition).
  3. Loop Expression: Specifies how values change between iterations.
The gem continues generating rows until the condition expression evaluates to false, at which point the generation process terminates.

​
Prerequisites

  • Add prophecy_basics package version 1.0.0 or higher to your project.

​
Input and Output

The GenerateRows gem accepts optional input data and produces one output dataset containing the generated rows.

​
Parameters

Configure the GenerateRows gem using the following parameters.
To reference input columns in the Condition expression and Loop expression fields, you must use the following format:

​
Expression examples

Reference the following examples to understand how to build different expressions for this gem. All expressions must be valid SQL. To reference the iterative value, use the output column name you defined.

​
Initialization expression examples

​
Condition expression examples

​
Loop expression examples

​
Gem examples

The following sections demonstrate different gem configurations and their corresponding outputs.

​
Create sequence

Assume you need to create a sequence of numbers from 1 to 10 for testing purposes or to generate row IDs. You want to create a simple numeric sequence that can be used as a foundation for other data operations. Use the following configuration:
  • Input dataset: none
  • Choose output column name: value
  • Initialization expression: 1
  • Condition expression: value <= 10
  • Loop expression (usually incremental): value + 1
  • Max rows per iteration: 100
If no input table is present, the following output table is generated:
The gem generates 10 rows with sequential values from 1 to 10. The process starts with the initialization value of 1, continues generating rows while the condition value <= 10 is true, and increments the value by 1 in each iteration.

​
Increment dates

Assume you want to generate a range of dates. To do so, you can use the following gem configuration:
  • Input dataset: none
  • Choose output column name: date
  • Initialization expression: '2024-01-01'
  • Condition expression: date <= '2024-01-07'
  • Loop expression (usually incremental): date + INTERVAL 1 DAY
This would generate 7 rows with consecutive dates from January 1st through January 7th, 2024.

​
Leverage input data

Assume you have input data that you want to use to influence the expressions in the gem configuration. You might have the following input table called Constants:
To leverage this table, you can use the following gem configuration:
  • Input dataset: Constants table
  • Choose output column name: sequence
  • Initialization expression: min_value
  • Condition expression: sequence <= max_value
  • Loop expression (usually incremental): sequence + step_size
This would produce the following output table:
The gem processes the input row and generates a sequence starting at 5, incrementing by 3, and continuing until reaching 20.