DSXchange

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.

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.

... 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).

Hmmm. As always best possible solution. Make sure you check "Preserve Sort Order" box in the transformer and "Duplicate To Retain" property to "Last" in Remove Duplicates stage.

ray.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). ...

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 :D

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.