horizontal pivoting using transformer
Moderators: chulett, rschirm, roy
horizontal pivoting using transformer
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,
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,
-
- Participant
- Posts: 111
- Joined: Mon Nov 30, 2009 7:21 am
- Location: Bangalore
Re: horizontal pivoting using transformer
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.
You could use @ITERATION <= 3 as looping condition.
Thanks
Poovlaingam.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
![Confused :?](./images/smilies/icon_confused.gif)
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
"You can never have too many knives" -- Logan Nine Fingers
Re: Reply
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.ssnegi wrote:Put three constraint links in transformer for 123,456,789...Then funnel them into one link with three records.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.HemaV wrote:i'm not sure on approach. trying to see anything can be done
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Reply
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.
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
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