Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett , rschirm , roy
sainath
Premium Member
Posts: 138 Joined: Fri Nov 19, 2004 3:57 pm
Post
by sainath » Fri Apr 25, 2014 2:41 pm
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
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
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Fri Apr 25, 2014 3:14 pm
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.
Last edited by
chulett on Sat Apr 26, 2014 7:54 am, edited 2 times in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
ssnegi
Participant
Posts: 138 Joined: Thu Nov 15, 2007 4:17 am
Location: Sydney, Australia
Post
by ssnegi » Fri Apr 25, 2014 9:53 pm
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
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Sat Apr 26, 2014 6:30 am
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
sainath
Premium Member
Posts: 138 Joined: Fri Nov 19, 2004 3:57 pm
Post
by sainath » Tue Apr 29, 2014 7:38 am
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
c
2 a
c
thks
RPhani
Participant
Posts: 32 Joined: Sun Aug 26, 2012 7:03 am
Location: Hyd
Post
by RPhani » Tue Apr 29, 2014 8:48 am
Hi,
I think the same code work for your scenario.
--------------------------------------
Phani
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Tue Apr 29, 2014 9:21 am
Exactly... doesn't change the answer.
-craig
"You can never have too many knives" -- Logan Nine Fingers
sainath
Premium Member
Posts: 138 Joined: Fri Nov 19, 2004 3:57 pm
Post
by sainath » Wed Apr 30, 2014 9:34 am
HI
Thanks for reply .Your solution worked