DSXchange

Hi,

I have a source file with data that has been laid out in a cross tab format; variable number of rows and columns.

eg

********* Name1 Name2 Name3
Date1 Address11 Address21 Address31
Date2 Address12 Address22 Address31
Date3 Address13 Address22 Address31


I need to convert it to

Date1 Name1 Address11
Date2 Name1 Address12
Date3 Name1 Address13
Date1 Name2 Address21
Date2 Name2 Address22
Date3 Name2 Address22
Date1 Name3 Address31
Date2 Name3 Address31


Any suggestions as to how I can achive the same. I have been going through the "Reformat Stages"; without much luck. Am I going in the wrong direction?

Thanks in advance for all feedback

sonia,

You can do it like this, Source file/table after that transformer hardcode the column names in that transformer and use the pivot stage.


source -----> transform -------> pivot stage ----->

Thanks

samba wrote:sonia,

You can do it like this, Source file/table after that transformer hardcode the column names in that transformer and use the pivot stage.


source -----> transform -------> pivot stage ----->

Thanks

In parallel ?

Yes in parallel job it self just now only i did it, i got the output as you expected

file -----> transform stage(Hard code the columns names)

NAME1
NAME2
NAME3
after that your input columns
date 1 column
address11 column
address21 column
address31 column

next pivot stage output you need to do like this

column 1 ----- take what ever in the date 1 column as it is
column 2 ----- mention hardcoded column names(name1,name2,name3)
column 3 ----- mention your address columns (address11,address21,address31)

at the end you will get the output..

Thanks

This is the output i got it. your also expecting same right

"Date2","NAME1","Address12"
"Date2","NAME2","Address22"
"Date2","NAME3","Address31 "
"Date1","NAME1","Address11"
"Date1","NAME2","Address21"
"Date1","NAME3","Address31 "
"Date3","NAME1","Address13"
"Date3","NAME2","Address22"
"Date3","NAME3","Address31 "

Looks like a job for a Pivot stage following a Transformer stage to me.

ray.wurlod wrote:Looks like a job for a Pivot stage following a Transformer stage to me. ...

Maybe its a really dumb Q!!

But how will I get Pivot on the pallete for Parallel.

and I would like to add one more hitch to the scenario. The number of columns are not fixed.

It should come under Processing stages when you install Datastage. I have version 7.5.1.A and I see it in my pallette.

splayer wrote:It should come under Processing stages when you install Datastage. I have version 7.5.1.A and I see it in my pallette.

sorry to come back to the topic after a long time.
We use 7.5.1.A, but the SERVER component is not available to us [we develop jobs only on parallel]. Is that why I do not get to see the PIVOT stage on the palette??

For the requirement a Buildop was developed to convert the file to a flattened format and works for me!

Thanks all

The Palette is fully customizable. You can find the parallel Pivot stage in the Stage Types branch of your Repository and either drag it to a design canvas from there, or customize your Palette so that it's there in future.

If you open the properties the stage type name is PivotPX. I have verified that it is installed on 7.5.1A.