.png){kind=logo}
Loop Iterator
-
DarkLight
Loop Iterator
-
DarkLight
Loop Iterator
The Loop Iterator component lets users loop over values of a simple sequence.
This component is a simple for loop. It enables you to run an attached component multiple times, each time with a unique value of an iteration variable. That variable can be referenced from the attached component.
To attach the iterator to another component, use the blue output connector and link to the desired component. To detach, right-click on the attached component and click Disconnect from Iterator.
If you need to iterate more than one component, put them into a separate orchestration job or transformation job and use a Run Transformation or Run Orchestration component attached to the iterator. In this way, you can run an entire ETL flow multiple times, once for each row of variable values.
All iterator components are limited to a maximum 5000 iterations.
Properties
| Property | Setting | Description |
|---|---|---|
| Name | String | A human-readable name for the component. |
| Concurrency | Select | Concurrent: Iterations are run concurrently. This requires all "Variables to Iterate" to be defined as copied variables, so that each iteration gets its own copy of the variable isolated from the same variable being used by other concurrent executions. Sequential: Iterations are done in sequence, waiting for each to complete before starting the next. This is the default setting. Note: The maximum concurrency is limited by the number of available threads (2x the number of processors on your cloud instance). |
| Variable to Iterate | Select | Choose an existing variable to iterate. Only numeric variables are available. |
| Starting Value | Integer | The starting value for the variable. Starting and ending values are both inclusive. |
| Increment Value | Integer | The increment value for the variable. This may be negative, providing the end value is smaller than the starting value. |
| End Value | Integer | The end value for the variable. Starting and ending values are both inclusive. |
| Break on Failure | Select | No: Attempt to run the attached component for each iteration, regardless of success or failure. This is the default setting. Yes: If the attached component does not run successfully, fail immediately. Note i: If a failure occurs during any iteration, the failure link is followed. This parameter controls whether it is followed immediately or after all iterations have been attempted. Note ii: This property is only available when Concurrency is set to Sequential. When set to Concurrent, all iterations will be attempted. |
| Record Values In Task History | Select | Choose whether to record iteration values in the Matillion ETL Task History. The default setting is Yes. |
| Stop On Condition | Select | Select Yes to stop the iteration based on a condition specified in the Condition property. The default setting is No. For this property to be available, set Concurrency to Sequential. |
| Mode | Select | Select the method of creating the condition. Simple: A no-code Condition UI will open, where users must specify an Input Variable, Qualifier, Comparator, and Value using drop-down menus and text fields. This is the default setting. Advanced: An editor will open, where users must write the condition manually using SQL. |
| Condition (Simple mode) | Input Variable | An input variable to form a condition around. |
| Qualifier | Is: Compares the input variable to the value using the comparator. Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc. |
|
| Comparator | Select the comparator. Available comparison operators include "Less than", "Less than or equal to", "Equal to", "Greater than or equal to", "Greater than", and "Blank". | |
| Value | Specify the value to be compared. | |
| Condition (Advanced mode) | Text Editor | Manually write the condition in the editor. This editor accepts conditions written in JavaScript. |
| Combine Conditions | Select | Use the defined conditions in combination with one another according to either And or Or. This property is only available when Mode is set to Simple. |
Variable Exports
This component makes the following values available to export into variables:
| Source | Description |
|---|---|
| Iteration Attempted | The number of iterations that this component attempts to reach (Max Iterations parameter). |
| Iteration Generated | The number of iterations that have been initiated. Iterators terminate after failure so this number will be the successful iterations, plus any potential failures. |
| Iteration Successful | The number of iterations successfully performed. This is the max iteration number, minus failures and any unattempted iterations (since the component terminates after failure). |
Example
In this example, we implement a simple Time-Series table.
Create Table is run once for each year from 2010 to the current year, resolving the iteration variable into the table name parameter. This creates a whole set of new tables.
First we must define a variable to iterate, and provide a sensible default value. When running a job or a component without iteration, this default value is used.
The iteration is configured to start at 2010 and iterate until the current year using an expression. (The expression syntax is JavaScript, and is covered here).
The Create Table component is set to only create if the existing table does not exist. Therefore, a new table will usually be skipped until the start of a new year.
Nested iteration is also possible, so we could nest two Loop Iterator components together to create tables based on year and quarter. Here we iterate year in the same way, then iterate from 1-4 for the quarters.
Please Note
JavaScript expressions are only available to use as parameter values within Matillion ETL components. Any valid single JavaScript expression may be used; however, it is recommended that only simple expressions are used. All variables defined in the job and / or environment should also be available to reference.
Important Information
When entering values into a component's parameter editor, everything enclosed within ${ } of the literal string will be evaluated immediately. This validation process currently does not take variables into account and may assume the value is incorrect.