How to make rows based on one column values

[vgundavarapu]

Hi All,

I have csv file as given below and I need to make csv file like example below, so could you please provide me idea how to get it using DataStage job stages.

CSV File:

EmpId EmpName KeyWords
100 Rick "Update,Delete"

Basically KeyWords needs to be put into separate rows as shown in the example given below. Key words may be 2 or more, we never know. I just given example with two key words to get idea.

EmpId EmpName KeyWords
100 Rick Update
100 Rick Delete


Thank you in advance.



Thanks,
Ven

[ssnegi]

You can achieve this by using transformer stage. In transformer stage use loop variables to loop though each line and extract the keywords one at a time, using comma as delimiter in field function. Then create multiple rows for each loop value of keyword.

[vgundavarapu]

Thank you very much for your reply.

Do you have any sample code for Transformer loop and Field function for your suggested solution.




Thanks,
Ven

[ssnegi]

Transformer
Loop Variables :
lvcnt : count(KeyWords,',')
lvfield : field(KeyWords,',',lvcnt+1)
lvLoop : if left(lvfield,1) <> '"' or right(lvfield,1) <> '"' then lvfield else if left(lvfield,1) = '"' then lvfield[2,len(lvfield)] else if right(lvfield,1) = '"' then lvfield[1,len(lvfield)-1] else SetNull()
Loop While : @ITERATION <= lvcnt + 1

Column Derivation :
Empid, EmpName, Keywords
Empid, EmpName, lvLoop

[vgundavarapu]

Thank you very much. the sample code is working fine for me.


Thanks,
Ven