Pivot Stage

[ravij]

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.

[kandyshandy]

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.

[anbu]

You can use transformer to achieve this.

Have three output links from transformer.

Assign
2010,JAN,100 to the first
2010,FEB,200 to the second link
2010,MARCH,300 to the third link

[chulett]

You don't need stage variables or the Field() function for this. Just use a transformer to introduce 3 new fields and hard-code their derivation to a month name. Pivot them along with their matching data column.

[kandyshandy]

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

[chulett]

Your example doesn't really change anything, what I posted doesn't require "a single row in input" to work correctly.

[ray.wurlod]

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.

[ravij]

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.wurlod]

You need YEAR, JAN_NAME, FEB_NAME, MAR_NAME, JANUARY, FEBRUARY, MARCH

Populate JAN_NAME, FEB_NAME and MAR_NAME with constants and pivot them.

[ravij]

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.