split comma separated values into different rows

Six Stars

split comma separated values into different rows

Hi , 

I have a scenario , which contains comma separated values in 2 cells , I need to normalize both Fields .Like , 

IDNameProductQty
1deepp1,p2,p3q1,q2,q3
2subhap11,p22,p33q11,q22,q33

 

I need the output as :

1deepp1q1
1deepp2q2
1deepp3q3
2Subhap11q11
2Subhap22q12
2Subhap33q13

 

# I tried with tNormalize ( 2times for Product , Qty Fields) but rows are getting repeated with that.

Please give me a solution for that.

 

Thanks in advance Smiley Happy

 

Regards

Subhadeep 

Eleven Stars

Re: split comma separated values into different rows

@subhadeep

 

Quest would be 

1) 1st Normalise Product Col , add  counter to each record number .

ID, Name , Product , Counter

1|John|p1|1
1|John|p2|2
1|John|p3|3

2|Shane|p11|1
2|Shane|p22|2
2|Shane|p33|3

2) Again for Quantity Col.

ID, Name , Quantity, Counter

1|John|q1|1
1|John|q2|2
1|John|q3|3

2|Shane|q11|1
2|Shane|q22|2
2|Shane|q33|3

 

3) Do inner join between three columns ID, Name , Counter

 

You should have your answer.

Regards
Abhishek KUMAR
Highlighted
Six Stars

Re: split comma separated values into different rows

Hi , 

Thanks for the response @akumar2301

In first Normalization , I did with "Product" , on 2nd one with "Qty" .

Then on each flow , I omitted Qty column( 1st flow) & Product column( 2nd flow) through tFilterColumn.

But , after that , in tJoin/tMap , I cannot map from 2 different filter Outputs , hence , cannot join 2 incoming flows.

Note : Solution without "Counter" column is desirable.

Capture.PNG

Eleven Stars

Re: split comma separated values into different rows

You cannot do that please follow below link

https://help.talend.com/reader/~V1UYTyXpjBKjP5PCMy4EA/khJeOaSeNMnFt0o~DMx6qQ

tJoin/tMap might not work for your case without an a counter. as for same id and name , you will have more than one entry in table.

Regards
Abhishek KUMAR

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog