crosstab source

sonia jacob · Post by **sonia jacob** » Mon Nov 06, 2006 2:43 pm

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

samba · Post by **samba** » Mon Nov 06, 2006 3:01 pm

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

sonia jacob · Post by **sonia jacob** » Mon Nov 06, 2006 3:05 pm

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

?

samba · Post by **samba** » Mon Nov 06, 2006 3:25 pm

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

samba · Post by **samba** » Mon Nov 06, 2006 3:27 pm

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 "

ray.wurlod · Post by **ray.wurlod** » Mon Nov 06, 2006 10:37 pm

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

sonia jacob · Post by **sonia jacob** » Tue Nov 07, 2006 7:06 am

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.

splayer · Post by **splayer** » Tue Nov 07, 2006 3:51 pm

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

sonia jacob · Post by **sonia jacob** » Mon Nov 20, 2006 11:52 am

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

ray.wurlod · Post by **ray.wurlod** » Mon Nov 20, 2006 2:22 pm

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.