You can use the Transformer stage to convert a single row for data with repeating columns
to multiple output rows.
Input data with multiple repeating columns
When the input data contains
rows with multiple columns containing repeating data, you can use the Transformer stage to produce
multiple output rows: one for each of the repeating columns.
For example, if the input row
contained the following data.
Col1 |
Col2 |
Name1 |
Name2 |
Name3 |
abc |
def |
Jim |
Bob |
Tom |
You can use the Transformer stage to flatten the input data and create multiple output
rows for each input row. The data now comprises the following columns.
Col1 |
Col2 |
Name |
abc |
def |
Jim |
abc |
def |
Bob |
abc |
def |
Tom |
To implement this scenario in the Transformer stage, make the following settings:
- Loop condition
- Enter the following expression as the loop
condition.
@ITERATION <= 3
Because each input row has three columns
containing names, you need to process each input row three times and create three separate output
rows.
- Loop variable
- Define a loop variable to supply the value for the new column Name in your output rows. The
value of LoopVar1 is set by the following
expression:
IF (@ITERATION = 1) THEN inlink.Name1
ELSE IF (@ITERATION = 2) THEN inlink.Name2
ELSE inlink.Name3
- Output link metadata and derivations
- Define the output link columns and their derivations:
- Col1 - inlink.col1
- Col2 - inlink.col2
- Name - LoopVar1