Documentation Index
Fetch the complete documentation index at: https://docs.prophecy.ai/llms.txt
Use this file to discover all available pages before exploring further.
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:
- Initialization Expression: Sets the starting value for the first row.
- Condition Expression: Defines when to stop generating rows (true/false condition).
- 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.
The GenerateRows gem accepts optional input data and produces one output dataset containing the generated rows.
| Port | Description |
|---|
| in0 | Optional input dataset. Columns from this dataset can be referenced in the gem expressions. - If an input dataset is provided, the gem generates a separate sequence of rows for each input row.
- If no input is provided, the gem generates a single sequence.
|
| out | Output dataset containing: - All original input columns (if input is provided)
- The new generated column with values created by the iterative expressions
Each input row may produce multiple output rows depending on the loop and condition expressions. |
Parameters
Configure the GenerateRows gem using the following parameters.
| Parameter | Description |
|---|
| Choose output column name | Name of the column that will contain the generated values. Each generated value in this column corresponds to one row created by the gem’s iterative process. |
| Configure row generation strategy | SQL logic for generating rows using three expressions: - Initialization expression: Sets the starting value for the generation process. This is the value of the first generated row.
- Condition expression: Determines how long to continue generating rows. Rows are added while this expression evaluates to true. Can reference input columns or the output column value.
- Loop expression (usually incremental): Specifies how the generated value changes each iteration. Typically an increment, but can be any expression that modifies the output column value.
|
| Max rows per iteration | Maximum number of rows generated per sequence. This limit prevents infinite loops or excessive memory usage.
Default: 100,000 rows. |
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
| Expression | Description |
|---|
1 | Start with the number 1 |
StartDate | Use a value from an input column named StartDate |
CURRENT_DATE() | Start with today’s date |
Condition expression examples
| Expression | Description |
|---|
value <= 10 | Generate rows while value is less than or equal to 10 |
value < MaxValue | Continue until value reaches a maximum from input column named MaxValue |
date <= EndDate | Generate dates until reaching an end date defined in an EndDate column |
Loop expression examples
| Expression | Description |
|---|
value + 1 | Increment by 1 each iteration |
value + StepSize | Increment by a variable step size defined in the input column StepSize |
date + INTERVAL 1 DAY | Add one day for date sequences |
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.
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:
min_value | max_value | step_size |
|---|
| 5 | 20 | 3 |
- 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:
min_value | max_value | step_size | sequence |
|---|
| 5 | 20 | 3 | 5 |
| 5 | 20 | 3 | 8 |
| 5 | 20 | 3 | 11 |
| 5 | 20 | 3 | 14 |
| 5 | 20 | 3 | 17 |
| 5 | 20 | 3 | 20 |
