a complex requirment.

arnie_nits · Post by **arnie_nits** » Wed Nov 11, 2009 1:06 am

Hi,

I have a complex requirment.

I have records like:

id Fe Zn Ag
1 10 20 30
2 5 6 8

Now I want the records like:

id test value
1 Fe 10
1 Zn 20
1 Ag 30
2 Fe 5
2 Zn 6
2 Ag 8

Can we do that in Datastage.....Please help...

Regards,
Arnie.

ray.wurlod · Post by **ray.wurlod** » Wed Nov 11, 2009 1:42 am

That requirement is for a "horizontal pivot" and is precisely what the Pivot stage provides.

ArndW · Post by **ArndW** » Wed Nov 11, 2009 1:54 am

Use stage variables to store the distinct column headers from the first row, i.e. SvarCol2 is "IF NOT(In.Col2) THEN In.Col2 ELSE SvarCol2". Then derive your output columns from the data and your stage variables. Manually add a line terminator after the column value and write to a sequential file or use the pivot stage to split the rows.

arnie_nits · Post by **arnie_nits** » Wed Nov 11, 2009 5:29 am

Well I am partly successful....

I am able to get..

Id Value
1 10
1 20
1 30
2 5
2 6
2 8

But was not able to get Fe,Zn,Ag...etc...as column values....

Regards,
Arnie.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Nov 11, 2009 5:45 am

If there are less columns (as 3 in your example), you can hardcode them in 3 separate links and funnel them together to get your output.

arnie_nits · Post by **arnie_nits** » Wed Nov 11, 2009 5:48 am

there are many columns......20 to be exact.....

Regards,
Arnab.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Nov 11, 2009 6:22 am

20 is not many.

arnie_nits · Post by **arnie_nits** » Wed Nov 11, 2009 6:41 am

Could u please explain the funnel logic....I guess Join will be more useful...

here is my requirment

I have records like:

id Fe Zn Ag
1 10 20 30
2 5 6 8

Now I want the records like:

id test value
1 Fe 10
1 Zn 20
1 Ag 30
2 Fe 5
2 Zn 6

ArndW · Post by **ArndW** » Wed Nov 11, 2009 7:06 am

My stage variable response also applies when you use the pivotr stage.

chulett · Post by **chulett** » Wed Nov 11, 2009 7:36 am

Or just include a hard-coded set of fields on the link before the pivot that you've set to the column names and pivot that along with the others.