Hi,
I am trying to achive a vertical Pivot in a transformer. My Input looks like this Col1 is the Key column and Col2 is the value column
Col1 Col2
A 1
A 2
A 3
B 4
B 5
B 6
I am trying to get the Output as where '-' is Null
Col1 Col2 Col3 Col4
A 1 - -
A 1 2 -
A 1 2 3
B 4 - -
B 4 5 -
B 4 5 6
I have a sort stage with a Key change column and used the below with stage variables in transformer
svColId = If inlink.keyChange Then 1 Else svColId + 1
Row1 = If inlink.keyChange Then inlink.Col2 Else Row1
Row2 = If inlink.keyChange
Then SetNull()
Else If svColId=2
Then inlink.Col2
Else Row2
Row3 = If inlink.keyChange
Then SetNull()
Else If svColId=3
Then inlink.Col2
Else Row3
And so on
now the Problem----output is correct for the first Key but from the second key the stage variables store the old value and outputs the same
The output comes like this
Col1 Col2 Col3 Col4
A 1 - -
A 1 2 -
A 1 2 3
B 4 2 3
B 4 5 3
B 4 5 6
I HAVE DONE ENOUGH RESEARCH IN THE FORUM WITH 'VERTICAL PIVOT' BUT UNABLE TO FIND THE ANSWER---- Please help.
Pivoting Problem
Moderators: chulett, rschirm, roy
Use an aggregator stage to get the count of the records for each group of keys (3 in your case). In a transformer have a variable count the input records. You will have to initiate it to 1 for every new group of key values. Constraint the output link to pass the record only when the counter equals the record count (3). And since the aggregator stage only pass the key fields, you will have to first sort the data split the data into to streams. One for the aggregator and one to the join stage. The output from the aggregator should be connected to join. After the join will be the transformer stage.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3
- Joined: Fri May 16, 2008 3:14 pm
Thanks Ray.....But seems like u didn't get the Problem....i know how to capture the Last row with the values..But my Problem was to flush the Stage variables for every key and initialize them again...I really did a lot of searching...May be i should have posted it briefly so that you could have read it completely....Before Answering :Dray.wurlod wrote:... or use a Remove Duplicates stage to take the final member of each group (which you would have found had you really done "enough" searching). ...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I did read it completely. If you take the output you have in your initial post and process it through a Remove Duplicates stage keyed on Col1 and Col2, retaining the Last record of each group, you will have the answer you desire. Which is precisely what I suggested.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.