I have a record with two columns .
Example :
Job Id | Job desc
1 | Service Manager,Training Insructor,Finance Manager
I need to split it in a way like
Job Id | Job desc
1 | Service Manager
1 | Training Insructor
1 | Finance Manager
Please help as to how I can achieve this . Thanks in advance
Split a column
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Parse into separate columns then use a Pivot stage. Probably the easiest parse is to combine the record into a single field using a Transformer stage then run that through a Column Import stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 132
- Joined: Tue Sep 04, 2007 11:38 am
- Location: NOIDA
Hi ,
Another way if you are sure that your second column will
have only two commas: -
SeqFILE -----> Tranformer ------> Funnel joining the 3 links------->SeqFILE
3 links
Inside the tranformer for each link use two columns : -
JOBID ----Derived from Input JOBID column
JOB------1)Field(DSLink3.JOBNAME,",",1) //for first link
2)Field(DSLink3.JOBNAME,",",2) //for second link
3)Field(DSLink3.JOBNAME,",",3) //for third link
Then merge the three links using a funnel. I hope it will solve your problem.
Another way if you are sure that your second column will
have only two commas: -
SeqFILE -----> Tranformer ------> Funnel joining the 3 links------->SeqFILE
3 links
Inside the tranformer for each link use two columns : -
JOBID ----Derived from Input JOBID column
JOB------1)Field(DSLink3.JOBNAME,",",1) //for first link
2)Field(DSLink3.JOBNAME,",",2) //for second link
3)Field(DSLink3.JOBNAME,",",3) //for third link
Then merge the three links using a funnel. I hope it will solve your problem.
-
- Participant
- Posts: 11
- Joined: Mon Feb 25, 2008 5:53 am
- Location: Hyderabad
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
U did not ask the question. Deepa_Vaidyanathan did. Can you please explain how a Pivot stage (presumably alone, based on what you said) can be used to solve this particular problem, rather than simply assert?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi Deepa,
I can suggest how we can do in Server edition , but i don how it works for parallel
In server
Step 1: Use Field function such that, Field(Read_Eg.Job_Desc,',',1) and so on till you have your end ',' ie if you have 10 ',' seperated then you have to use 10 field functions and make all these field function as Stage Variables(i.e. N(for eg 10) number of stage Variable SV1 to SV10)
Step 2:
In the output of transformer have N number of output for this particular column Job_Desc as Job_Desc1, Job_Desc2 and so on...having a direct link from Stage Variable ie SV1 to Job_Desc1
Step3:
Use a Pivot Stage,
In the Input you will be having Job_Id and Job_Desc(N number of Job_Desc) in our eg 10, so a total of N+1 (ie 11 in our eg). In the Output tab of PIVOT stage just keep two columns as Job_Id and Job_Desc.
Where the derivation of Job_Desc being Job_Desc1;Job_Desc2;Job_Desc3;Job_Desc4 and so on.
Do not give any derivation for Job_ID.
Step 4:
Write the output to the required target.
Thus you can seperate
Job Id | Job desc
1 | Service Manager,Training Insructor,Finance Manager
into
Job Id | Job desc
1 | Service Manager
1 | Training Insructor
1 | Finance Manager
I can suggest how we can do in Server edition , but i don how it works for parallel
In server
Step 1: Use Field function such that, Field(Read_Eg.Job_Desc,',',1) and so on till you have your end ',' ie if you have 10 ',' seperated then you have to use 10 field functions and make all these field function as Stage Variables(i.e. N(for eg 10) number of stage Variable SV1 to SV10)
Step 2:
In the output of transformer have N number of output for this particular column Job_Desc as Job_Desc1, Job_Desc2 and so on...having a direct link from Stage Variable ie SV1 to Job_Desc1
Step3:
Use a Pivot Stage,
In the Input you will be having Job_Id and Job_Desc(N number of Job_Desc) in our eg 10, so a total of N+1 (ie 11 in our eg). In the Output tab of PIVOT stage just keep two columns as Job_Id and Job_Desc.
Where the derivation of Job_Desc being Job_Desc1;Job_Desc2;Job_Desc3;Job_Desc4 and so on.
Do not give any derivation for Job_ID.
Step 4:
Write the output to the required target.
Thus you can seperate
Job Id | Job desc
1 | Service Manager,Training Insructor,Finance Manager
into
Job Id | Job desc
1 | Service Manager
1 | Training Insructor
1 | Finance Manager
-
- Premium Member
- Posts: 145
- Joined: Fri Sep 21, 2007 9:35 am
- Location: Boston