PIVOT STAGE

ghutchin · Post by **ghutchin** » Tue Dec 04, 2012 3:39 pm

hI

source data

key column   NAME1  NAME 2   LAST NAME   NAME_TYPE
123          ABC    DEF      GHI         P-PRIMARY
123          XYS    XXX      YYY         A--ALIAS
123          WWW    BBB      AAA         A--ALIAS

Output:::

I want to have all ALIAS names from ROWS to COLUMNS ..

I used the pivot stage and set ARRAY to 10 ..and group by Key column

But i am getting following error .please advice

Operator terminated abnormally: received signal SIGFPE

chulett · Post by **chulett** » Tue Dec 04, 2012 4:04 pm

I'm not sure I follow what exactly it is you mean by "all ALIAS names from ROWS to COLUMNS". Can you show us a sample of the output you need based on the sample input you posted? Meaning what it should look like pivoted? Thanks.

ghutchin · Post by **ghutchin** » Tue Dec 04, 2012 8:26 pm

key column NAME1 NAME 2 LAST NAME NAME_TYPE
123 ABC DEF GHI P XYS XXX YYY A WWW BBB AA A

My goal is to move all the Alias Names (Name_type =A) from ROWS to COLUMNS.so that all apear in one row with unique key value

thks

ray.wurlod · Post by **ray.wurlod** » Tue Dec 04, 2012 11:32 pm

Since you need transformation to extract the alias names, this task might more readily be performed using the group processing capabilities of the Transformer stage. Are you running version 8.7 or higher?

bhasds · Post by **bhasds** » Wed Dec 05, 2012 2:21 am

Hi ghutchin,

You may try this in stage variable-

Code: Select all

If ME1 <> SV2 Then NAME1 :" ": NAME2 :" ": LAST Else If  Index(NAME_TYPE,"ALIAS",1)>0 Then SV1 :" ":NAME1 :" ":NAME2:" ":LAST:" ": Left(NAME_TYPE,1) Else @FALSE---->SV1
ME1--->SV2

ghutchin · Post by **ghutchin** » Wed Dec 05, 2012 10:38 am

HI

I am using 8,7 .

Can you explain in detail ..I did not understand previous post.

What is ME1?

bhasds · Post by **bhasds** » Wed Dec 05, 2012 11:23 am

Please find the corrected code as below-
In stage variable in transformer-

Code: Select all

If key column  <> SV2 Then NAME1 :" ": NAME2 :" ": LAST NAME:" ":Left(NAME_TYPE,1) Else If  Index(NAME_TYPE,"ALIAS",1)>0 Then SV1 :" ":NAME1 :" ":NAME2:" ":LAST NAME:" ": Left(NAME_TYPE,1) Else @FALSE
--->SV1 
key column --->SV2

ghutchin · Post by **ghutchin** » Wed Dec 05, 2012 12:29 pm

Hi

I want output in seperate columns ..not concatinated in one column ..

I was trying using PIVOT stage (vertical) group by keycolumn,NAME_TYPE and filtered only Alias Names.

but it is giving error .../bin/echo: write error: Broken pipe

bhasds · Post by **bhasds** » Wed Dec 05, 2012 1:56 pm

I am not sure why you are getting the error

giving error .../bin/echo: write error: Broken pipe

If this

key column NAME1 NAME 2 LAST NAME NAME_TYPE
123 ABC DEF GHI P XYS XXX YYY A WWW BBB AA A

is your desired output-
Then the posted code will give you the output which can be read in separate columns with space delimiter.
OR
You can use column import stage on the stage variable output to get the out put in separate columns.

prasson_ibm · Post by **prasson_ibm** » Thu Dec 06, 2012 3:08 am

Hi,

This can be easily done by using Pivot Enterprise stage and using vertical pivot option in DS 8.7.

Keep KeyCol as Group By and rest columns as pivot option and make Array size as 3.

After pivoting you can put transformer stage to achieve your goal.

Thanks
Prasoon