horizontal pivoting using transformer

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

Post Reply
HemaV
Participant
Posts: 63
Joined: Wed Jan 09, 2008 1:38 am
Location: Bangalore

horizontal pivoting using transformer

Post by HemaV »

Hi All,

Needed a logic in transformer for the below scenario. i shouldn't use pivot stage. and needed in datastage and not using unix.

I/P data:
Account_numb Name Total
123 ABCDEF 123456789
345 GHIJKLMN 987654321

O/P data:
Account_numb Name Total
123 AB 123
123 CD 456
123 EF 789
345 GH 987
345 IJ 654
345 KL 321
345 MN NULL

Thanks in Advance,
dsetlteam
Premium Member
Premium Member
Posts: 35
Joined: Mon Feb 10, 2014 10:14 pm
Location: USA

Post by dsetlteam »

Try using loop variables (if you are on 8.5 or above version of datastage) in the transformer stage to get the required output
Poovalingam
Participant
Posts: 111
Joined: Mon Nov 30, 2009 7:21 am
Location: Bangalore

Re: horizontal pivoting using transformer

Post by Poovalingam »

You could try looping option in transformer stage to convert every single row to multiple rows based on our requirement.

You could use @ITERATION <= 3 as looping condition.

Thanks
Poovlaingam.
HemaV
Participant
Posts: 63
Joined: Wed Jan 09, 2008 1:38 am
Location: Bangalore

Post by HemaV »

Is there any logic without @Iteration and looping functions. can we implement using stage variables?

Thanks
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

What have YOU tried?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

What are your Business Requirements? Specifically wondering about the rules for the "Name" and "Total" field chopping up you seem to be doing but that's always a hard call to make when someone posts completely made up "example" data with no requirements. Right now it doesn't make all that much sense - every two characters from Name match up to three characters of Total? :?

And as asked before, what have you tried so far? What issues are you facing?
-craig

"You can never have too many knives" -- Logan Nine Fingers
HemaV
Participant
Posts: 63
Joined: Wed Jan 09, 2008 1:38 am
Location: Bangalore

Post by HemaV »

i'm not sure on approach. trying to see anything can be done
ssnegi
Participant
Posts: 138
Joined: Thu Nov 15, 2007 4:17 am
Location: Sydney, Australia

Reply

Post by ssnegi »

Put three constraint links in transformer for 123,456,789...Then funnel them into one link with three records.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Re: Reply

Post by chulett »

ssnegi wrote:Put three constraint links in transformer for 123,456,789...Then funnel them into one link with three records.
Why 3? I don't see anything that notes it will always / only be three, matter of fact the second set of data shows 4 occurrences.
-craig

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

HemaV wrote:i'm not sure on approach. trying to see anything can be done
Fine. Again - what are your Business Requirements / transformation rules? Explain in words how to perform the splitting of the two columns so that people can stop guessing what you need done.
-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

Reply

Post by ssnegi »

You have to divide the data based on constraints. These should be as many as the maximum number of divisions. So if there are 4 maximum rows then 4 constraints. Then funnel these constraint links to collect them into rows.
bhasds
Participant
Posts: 79
Joined: Thu May 27, 2010 1:49 am

Post by bhasds »

Hi HemaV,

The scenario can be solved by using loop variable.

In iteration-

@ITERATION <= Len(Reference.col2) /2

Take 2 loop variables-
Derivation LoopVariable
If @ITERATION <=1 Then LV1 Else LV1 +2 LV1(initialize with 2)

If @ITERATION <=1 Then LV2 Else LV2 +3 LV2(initialize with 3)

Output Derivation-

Derivation ColName

Col1 Col1
Right(Left(Reference.col2, LV1),2) Col2
If len(StripWhiteSpace(Right(Left(Reference.col3, LV2),3))) <3 Then "" Else Right(Left(Reference.col3, LV2),3) Col3


Regards
Bhasds
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

... or we could just continue to guess based on the wonderful example data.
-craig

"You can never have too many knives" -- Logan Nine Fingers
thompsonp
Premium Member
Premium Member
Posts: 205
Joined: Tue Mar 01, 2005 8:41 am

Post by thompsonp »

chulett wrote:... or we could just continue to guess based on the wonderful example data.
Sounds fun. How many guesses are we allowed? Is there a prize? :wink:
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Oh yes... a very special prize. <wink,wink>
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply