Pivot Stage
Moderators: chulett, rschirm, roy
Pivot Stage
Hi,
I have a different requirement. My input is like
Year, JAN,FEB,MARCH
2010,100,200,300
and my Output should be like
Year, Month, Amount
2010,JAN,100
2010,FEB,200
2010,MARCH,300
I tried in this forum, but could not find the relevant solution.
Please throw some light on this issue.
Thanks in advance.
I have a different requirement. My input is like
Year, JAN,FEB,MARCH
2010,100,200,300
and my Output should be like
Year, Month, Amount
2010,JAN,100
2010,FEB,200
2010,MARCH,300
I tried in this forum, but could not find the relevant solution.
Please throw some light on this issue.
Thanks in advance.
Ravi
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
I would suggest you to do this in a server than in a PX. But i am not saying that it is not feasible in a PX job. May be, it will need some effort from your end.
In server or PX, pivot stage will give you the output as shown below
2010, 100
2010, 200
2010, 300
All you need to do after getting this output is to introduce corresponding COLUMN name in each row between YEAR and AMOUNT. You can do that by defining a stage variable.
StageVar = "JAN,FEB,MAR,APR,MAY,....,DEC"
Now you can use FIELD function to get JAN for the first row, FEB for the second row and so on.. when the year changes, you need to go for JAN again and so on. In PX, you need to partition by year and follow the same logic. This is just a clue but not the solution.
In server or PX, pivot stage will give you the output as shown below
2010, 100
2010, 200
2010, 300
All you need to do after getting this output is to introduce corresponding COLUMN name in each row between YEAR and AMOUNT. You can do that by defining a stage variable.
StageVar = "JAN,FEB,MAR,APR,MAY,....,DEC"
Now you can use FIELD function to get JAN for the first row, FEB for the second row and so on.. when the year changes, you need to go for JAN again and so on. In PX, you need to partition by year and follow the same logic. This is just a clue but not the solution.
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
I assumed that the poster would have data like shown below, though he has given one row as an example.
YEAR JAN FEB MAR....
2010 100 200 300
2011 400 500 600
2012 250 350 450
etc. In this above case, hardcording will not help and that's why i suggested stage variable.
Anyways, if it is a single row in input, i would also go for hardcoding![Wink ;)](./images/smilies/icon_wink.gif)
YEAR JAN FEB MAR....
2010 100 200 300
2011 400 500 600
2012 250 350 450
etc. In this above case, hardcording will not help and that's why i suggested stage variable.
Anyways, if it is a single row in input, i would also go for hardcoding
![Wink ;)](./images/smilies/icon_wink.gif)
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Use a Column Generator stage to generate three new fields, which contain the month name abbreviations. Pivot these into "Month_Name" field as well as pivoting the monthly value columns.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Thanks for your Reply friends.
Ray, i tried with your suggestion, but could not get proper output. will explain what I did exactly.
source is :
YEAR, JANUARY,FEBRUARY,MARCH
2009,100,200,300
etc..
Output should be :
YEAR,MONTH,AMOUNT
2009,JANUARY,100
2009,FEBRUARY,200
2009,MARCH,300
I took col generator stage and created 3 columns(JAN,FEB,MAR). I cannot take full name of month as i have them alreay in my source. thats why i took abbreviations of those months. Then in Pivot stage took a col 'MONTH_NAME' and defined the cols as JAN,FEB,MAR in 'Derivation' field and also took 'AMOUNT' col and defined it with 'JANUARY,FEBRUARY,MARCH' in derivation field.
When execute the job, getting some different values for MONTH_NAME column(like a, aaa, a,aaa). How would I get Month Names which i defined in col gen stage in MONTH_NAMES column.
execuse for lengthy description.
please help me in resolving this issue.
thanks in advance.
Ray, i tried with your suggestion, but could not get proper output. will explain what I did exactly.
source is :
YEAR, JANUARY,FEBRUARY,MARCH
2009,100,200,300
etc..
Output should be :
YEAR,MONTH,AMOUNT
2009,JANUARY,100
2009,FEBRUARY,200
2009,MARCH,300
I took col generator stage and created 3 columns(JAN,FEB,MAR). I cannot take full name of month as i have them alreay in my source. thats why i took abbreviations of those months. Then in Pivot stage took a col 'MONTH_NAME' and defined the cols as JAN,FEB,MAR in 'Derivation' field and also took 'AMOUNT' col and defined it with 'JANUARY,FEBRUARY,MARCH' in derivation field.
When execute the job, getting some different values for MONTH_NAME column(like a, aaa, a,aaa). How would I get Month Names which i defined in col gen stage in MONTH_NAMES column.
execuse for lengthy description.
please help me in resolving this issue.
thanks in advance.
Ravi
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Thanks alot Ray,
Your logic worked out. I implemented this logic and its giving result.
As you said, I took JAN_NAME, FEB_NAME and MAR_NAME columns in Transformer stage and populatied constant values like JANUARY, FEBRUARY and MARCH respectively, then used Pivot stage.
Once again thanks to all who helped in this issue.
Your logic worked out. I implemented this logic and its giving result.
As you said, I took JAN_NAME, FEB_NAME and MAR_NAME columns in Transformer stage and populatied constant values like JANUARY, FEBRUARY and MARCH respectively, then used Pivot stage.
Once again thanks to all who helped in this issue.
Ravi