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
horserider
Participant
Posts: 71 Joined: Mon Jul 09, 2007 1:12 pm
Post
by horserider » Fri Dec 21, 2007 8:14 am
I have a source file that has 18 columns, 2 of them make Primary Key. I want to convert some of the columns in a row to multiple rows. Example below.
INPUT SOURCE FILE
PKey CustNum Add_1 Tel_1 Zip_1 Add_2 Tel_2 Zip_2 Add_3 Zip_3 Tel_3 Total_Amt Total_Rebate
OUTOUT FILE DESIRED AS
PKey CustNum Add_1 Tel_1 Zip_1 Tot_Amt Tot_Rebate
PKey CustNum Add_2 Tel_2 Zip_2 Tot_Amt Tot_Rebate
PKey CustNum Add_3 Tel_3 Zip_3 Tot_Amt Tot_Rebate
Basically for 3 set of Address Tel and Zip in 1 ROW, I want to create 3 rows that has each set in 1 row?
Any way to get this done in Parallel Job?
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Fri Dec 21, 2007 8:21 am
Pivot stage.
-craig
"You can never have too many knives" -- Logan Nine Fingers
uegodawa
Participant
Posts: 71 Joined: Thu Apr 27, 2006 12:46 pm
Post
by uegodawa » Fri Dec 21, 2007 9:45 am
Use a Pivot stage and define the OUTPUT of Pivot as follows
1. PKey
2.CustNum
3. Address
4. Telephone
5. Zip
6. Total_Amt
7.Total_Rebate
Use the following derivations;
Address : Add_1,Add_2, Add_3
Telephone : Tel_1 , Tel_2, Tel_3
Zip : Zip_1,Zip_2, Zip_3
Thanks,
Upul
relati
Participant
Posts: 17 Joined: Mon Oct 01, 2007 12:44 pm
Post
by relati » Fri Dec 21, 2007 10:56 am
You could accomplish this using a Transformer stage with multiple output links, each output containing the desired fields.
horserider
Participant
Posts: 71 Joined: Mon Jul 09, 2007 1:12 pm
Post
by horserider » Fri Dec 21, 2007 1:51 pm
Yes I can very well do that. The only problem is if the columns I have to make into rows is too much then I will have too many transformers. Pivot will work best for me.
jshurak
Participant
Posts: 74 Joined: Mon Jan 09, 2006 12:39 pm
Post
by jshurak » Thu Dec 27, 2007 7:26 am
can the pivot stage do the opposite. I have a query that returns 11 rows in one column. I'd like to split those up into 11 columns and one row.
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Thu Dec 27, 2007 7:39 am
Allegedly 'yes' in version 8.x but otherwise - no.
-craig
"You can never have too many knives" -- Logan Nine Fingers
jshurak
Participant
Posts: 74 Joined: Mon Jan 09, 2006 12:39 pm
Post
by jshurak » Thu Dec 27, 2007 8:18 am
is there any stage that can do that?
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Thu Dec 27, 2007 8:50 am
Typically using manual 'aggregation' with stage variable concatenation in a Transformer. Search for 'vertical pivot' for discussions on the subject.
-craig
"You can never have too many knives" -- Logan Nine Fingers
jshurak
Participant
Posts: 74 Joined: Mon Jan 09, 2006 12:39 pm
Post
by jshurak » Thu Dec 27, 2007 9:26 am
thanks!