Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I have a scenario , which contains comma separated values in 2 cells , I need to normalize both Fields .Like ,
ID | Name | Product | Qty |
1 | deep | p1,p2,p3 | q1,q2,q3 |
2 | subha | p11,p22,p33 | q11,q22,q33 |
I need the output as :
1 | deep | p1 | q1 |
1 | deep | p2 | q2 |
1 | deep | p3 | q3 |
2 | Subha | p11 | q11 |
2 | Subha | p22 | q12 |
2 | Subha | p33 | q13 |
# 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
Regards
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.
Hi ,
Thanks for the response @uganesh
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.
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.
That is possible using split( ) function in java
See the code below,
String array1[]=row1.CS_String .split(",");
for (int i=0 ; i<array1.length ; i++)
{
row2.Id = row1.Id;
row2.CS_String_Element= array1[i];
}
I tried with input:
col 1- Id : 1
col2- CS_String : "A,B,C,D,E,F,G"
Here is the output: