Page 1 of 1
vertical pivot
Posted: Fri Apr 25, 2014 2:41 pm
by 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 a
![:b:](//twemoji.maxcdn.com/2/svg/1f171.svg)
c
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
Posted: Fri Apr 25, 2014 3:14 pm
by 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.
Reply
Posted: Fri Apr 25, 2014 9:53 pm
by 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.
Re: Reply
Posted: Sat Apr 26, 2014 6:30 am
by chulett
ssnegi wrote: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.
Posted: Tue Apr 29, 2014 7:38 am
by 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 a
![:b:](//twemoji.maxcdn.com/2/svg/1f171.svg)
c
2 a
![:b:](//twemoji.maxcdn.com/2/svg/1f171.svg)
c
thks
Posted: Tue Apr 29, 2014 8:48 am
by RPhani
Hi,
I think the same code work for your scenario.
--------------------------------------
Phani
Posted: Tue Apr 29, 2014 9:21 am
by chulett
Exactly... doesn't change the answer.
Posted: Wed Apr 30, 2014 9:34 am
by sainath
HI
Thanks for reply .Your solution worked