vertical pivot

[sainath]

Hi

I want to convert data from rows to columns for same key value .It can have N number of values .
Input
Key
1 a
1 b
1 c
1 to N
Output
1 ac

I cannot use vertical pivot because I dont know array size.

I also tried to transformation loop with loopvar <= MAX(COUNT) .But it is looping same value

Can you share your ideas

thks

[chulett]

You would use Transformer Looping to go the other direction, columns to rows. This is transformer concatenation and Remove Duplicates, retaining the last duplicate.

Edited to clarify the fact that by 'aggregation' I meant aggregate the values in the transformer via concatenation, not the stage of the same name.

[ssnegi]

Transformer : Key hash partition sort ascending
Value (a,b,c etc) No partition Sort only ascending
Stage Variables :
svValue : if svKey = Input.Key then svValueOld:':':Input.Value else Input.Value
svKey : Input.Key
svValueOld : svValue

Then Remove Duplicate stage with Same partition.
Key : Key Duplicate to retain : Last.

[chulett]

Stage Variables :
svValue : if svKey = Input.Key then svValueOld:':':Input.Value else Input.Value
svKey : Input.Key
svValueOld : svValue

You only need two as there's no need to save the 'old' value, just the key:

Code: Select all

svValue:  if Input.Key = svKeyOld then svValue:':':Input.Value else Input.Value 
svKeyOld: Input.Key

And a key change column would allow the use of a single stage variable.

[sainath]

Hi

Thanks for your reply.My mistake I did not explain correclty on my previous email .I updated my requirement .I will have different keys.


Some how i have to add a logic to only append the rows when there is same key .

Input
Key
1 a
1 b
1 c
1 to N
2 a
2 b
2 c
2 to N
Output
1 ac
2 ac


thks

[RPhani]

Hi,

I think the same code work for your scenario.

--------------------------------------
Phani

[chulett]

Exactly... doesn't change the answer.

[sainath]

HI

Thanks for reply .Your solution worked