Page 1 of 1
PIVOT STAGE
Posted: Tue Dec 04, 2012 3:39 pm
by ghutchin
hI
source data
Code: Select all
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
Posted: Tue Dec 04, 2012 4:04 pm
by chulett
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.
Posted: Tue Dec 04, 2012 8:26 pm
by ghutchin
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
Posted: Tue Dec 04, 2012 11:32 pm
by ray.wurlod
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?
Posted: Wed Dec 05, 2012 2:21 am
by bhasds
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
Posted: Wed Dec 05, 2012 10:38 am
by ghutchin
HI
I am using 8,7 .
Can you explain in detail ..I did not understand previous post.
What is ME1?
Posted: Wed Dec 05, 2012 11:23 am
by bhasds
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
Posted: Wed Dec 05, 2012 12:29 pm
by ghutchin
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
Posted: Wed Dec 05, 2012 1:56 pm
by bhasds
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.
Posted: Thu Dec 06, 2012 3:08 am
by prasson_ibm
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