Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett , rschirm , roy
ghutchin
Premium Member
Posts: 45 Joined: Thu Mar 13, 2003 7:34 am
Post
by ghutchin » Tue Dec 04, 2012 3:39 pm
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
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
ghutchin
Premium Member
Posts: 45 Joined: Thu Mar 13, 2003 7:34 am
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
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
bhasds
Participant
Posts: 79 Joined: Thu May 27, 2010 1:49 am
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
Premium Member
Posts: 45 Joined: Thu Mar 13, 2003 7:34 am
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
Participant
Posts: 79 Joined: Thu May 27, 2010 1:49 am
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
Premium Member
Posts: 45 Joined: Thu Mar 13, 2003 7:34 am
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
Participant
Posts: 79 Joined: Thu May 27, 2010 1:49 am
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
Premium Member
Posts: 536 Joined: Thu Oct 11, 2007 1:48 am
Location: Bangalore
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