Transformer Looping Functions for Pivoting

Transformer Looping Functions for Pivoting the data :

Convert a single row into multiple rows using Transformer Looping Function? (Pivoting of data using parallel transformer in Datastage 8.5,8.7 and 9.1)

Refer This link for more details : Looping Concept in Datastage

Now you can argue that this is possible using a pivot stage. But for the sake of this article lets try doing this using a Transformer!

Below is a screenshot of our input data

We are going to read the above data from a sequential file and transform it to look like this

So lets get to the job design

Step 1: Read the input data.

Step 2: Logic for Looping in Transformer Properties

In the adjacent image you can see a new box called Loop Condition. This where we are going to control the loop variables.

Below is the screenshot when we expand the Loop Condition box

                            

The Loop While constraint is used to implement a functionality similar to “WHILE” statement in programming. So, similar to a while statement need to have a condition to identify how many times the loop is supposed to be executed.

To achieve this @ITERATION system variable was introduced. 

In our example we need to loop the data 3 times to get the column data onto subsequent rows.

So lets have @ITERATION <=3

Now create a new Loop variable with the name LoopName 

The derivation for this loop variable should be

If @ITERATION=1 Then DSLink2.Name1 

Else If @ITERATION=2 Then DSLink2.Name2 

Else DSLink2.Name3

Below is a screenshot illustrating the same

Now all we have to do is map this Loop variable Loop Name to our output column Name

Lets map the output to a sequential file stage and see if the output is a desired.

 

After running the job, we did a view data on the output stage and here is the data as desired.

Making some tweaks to the above design we can implement things like

1. Adding new rows to existing rows 

2. Splitting data in a single column to multiple rows and many more such stuff..