DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
UAUITSBI



Group memberships:
Premium Members

Joined: 13 Aug 2009
Posts: 115
Location: University of Arizona
Points: 1834

Post Posted: Fri Dec 19, 2014 10:58 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Unix
Hello,

A quick question on pivoting:

I have rows as below from the source:

Code:
SchoolYear   Site     Q1A   Q1B   Q1C
   1112        4731      5       5       5
   1112        0641      5       5       5
   1112        3881      2       4       4


I am trying to achieve below output:

Code:
SchoolYear   Site     Questions   Responses
   1112        4731        Q1A             5
   1112        4731        Q1B             5
   1112        4731        Q1C             5 
   1112        0641        Q1A             5
   1112        0641        Q1B             5
   1112        0641        Q1C             5     
   1112        3881        Q1A             2
   1112        3881        Q1B             4
   1112        3881        Q1C             4


I have implemented looping in transformer but I was able to achieve the following and yes I have checked the other posts but couldn't really get the solution.

Code:
SchoolYear   Site     Questions   
   1112        4731        Q1A             
   1112        4731        Q1B             
   1112        4731        Q1C               
   1112        0641        Q1A             
   1112        0641        Q1B             
   1112        0641        Q1C                 
   1112        3881        Q1A             
   1112        3881        Q1B             
   1112        3881        Q1C
   1112        4731        5             
   1112        4731        5             
   1112        4731        5               
   1112        0641        5             
   1112        0641        5             
   1112        0641        5                 
   1112        3881        2             
   1112        3881        4             
   1112        3881        4           


Any advice is appreciated !!

Thanks !!


Last edited by UAUITSBI on Mon Dec 22, 2014 10:58 am; edited 4 times in total
UAUITSBI



Group memberships:
Premium Members

Joined: 13 Aug 2009
Posts: 115
Location: University of Arizona
Points: 1834

Post Posted: Fri Dec 19, 2014 11:19 am Reply with quote    Back to top    

Forgot to mention that I have used @ITERATION variable in the looping and i have 59 columns not just 3 so I have to write

Code:
If @ITERATION = 1 Then "Q1A" Else 
If @ITERATION = 2 Then "Q1B" Else 
If @ITERATION = 3 Then "Q1C" Else etc..

The above "If then else" is tedious as I have to repeat for 59 columns and I am not sure if this is right approach, please advise !
Rate this response:  
Not yet rated
UAUITSBI



Group memberships:
Premium Members

Joined: 13 Aug 2009
Posts: 115
Location: University of Arizona
Points: 1834

Post Posted: Mon Dec 22, 2014 11:16 am Reply with quote    Back to top    

Is this a combination of Vertical pivoting and Horizontal pivoting ?
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43036
Location: Denver, CO
Points: 222146

Post Posted: Mon Dec 22, 2014 9:55 pm Reply with quote    Back to top    

No, this is just a horizontal pivot. I don't see any need off the top of my head for transformer looping and there are other posts here that discuss how to pivot 'column headings' along with their ass ...

_________________
-craig

I've all my wisdom teeth, Two up top, two beneath
And yet I'll recognize my mouth says things that aren't so wise


Last edited by chulett on Tue Dec 23, 2014 4:53 pm; edited 1 time in total
Rate this response:  
Not yet rated
rschirm

Premium Poster



Group memberships:
Premium Members, Inner Circle

Joined: 13 Dec 2002
Posts: 27

Points: 203

Post Posted: Mon Dec 22, 2014 10:36 pm Reply with quote    Back to top    

Just use the enterprise pivot stage to do exactly what you want to do.
Rate this response:  
Not yet rated
UAUITSBI



Group memberships:
Premium Members

Joined: 13 Aug 2009
Posts: 115
Location: University of Arizona
Points: 1834

Post Posted: Tue Dec 23, 2014 9:15 pm Reply with quote    Back to top    

Thanks for the response chulett. I have achieved this in transformer looping with a simple If then else logic using @iteration variable. Earlier I used horizontal pivot through pivot stage but I was not able to able to pivot the responses. By using the same logic for responses I pivoted those as well in the looping.
Rate this response:  
Not yet rated
UAUITSBI



Group memberships:
Premium Members

Joined: 13 Aug 2009
Posts: 115
Location: University of Arizona
Points: 1834

Post Posted: Tue Dec 23, 2014 9:30 pm Reply with quote    Back to top    

Thanks for the response rschirm. I have tried with pivot enterprise stage in which I was able to pivot the questions but couldn't pivot the responses as they are getting pivoted along with the questions as they are interrelated.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43036
Location: Denver, CO
Points: 222146

Post Posted: Tue Dec 23, 2014 11:46 pm Reply with quote    Back to top    

You may have achieved this using transformer looping but what did you actually achieve? Not the desired end result. As already noted, it's a horizon pivot of columns to rows.

_________________
-craig

I've all my wisdom teeth, Two up top, two beneath
And yet I'll recognize my mouth says things that aren't so wise
Rate this response:  
Not yet rated
priyadarshikunal



Group memberships:
Premium Members

Joined: 01 Mar 2007
Posts: 1735
Location: Troy, MI
Points: 9319

Post Posted: Wed Dec 24, 2014 4:40 am Reply with quote    Back to top    

As you are hard coding Q1A Q1B Q1C, do you have only 3 columns you need to pivot? then why not just take 3 output from and transformer and funnel it back?

And as already noted multiple times by Craig, Its a horizontal pivot.

_________________
Priyadarshi Kunal

Genius may have its limitations, but stupidity is not thus handicapped. Wink
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43036
Location: Denver, CO
Points: 222146

Post Posted: Wed Dec 24, 2014 8:27 am Reply with quote    Back to top    

59 columns... but still.

_________________
-craig

I've all my wisdom teeth, Two up top, two beneath
And yet I'll recognize my mouth says things that aren't so wise
Rate this response:  
Not yet rated
priyadarshikunal



Group memberships:
Premium Members

Joined: 01 Mar 2007
Posts: 1735
Location: Troy, MI
Points: 9319

Post Posted: Mon Dec 29, 2014 5:42 am Reply with quote    Back to top    

dang, missed that 59 column part in second post Embarassed

_________________
Priyadarshi Kunal

Genius may have its limitations, but stupidity is not thus handicapped. Wink
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours