split comma separated values into different rows

Five 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 

Seven 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
( Please mark the post as resolved if you found your solution )
Five 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

Seven 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
( Please mark the post as resolved if you found your solution )

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.