Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to concatenate multiple comma-separated lists, as in the following example.
-> INPUT
ID | ColA | ColB | ColC |
--------------------------------------------------------------
1 | 1A | 1B | 1C |
2,3,4 | 2A, 3A, 4A | 2B, 3B, 4B | 2C, 3C, 4C |
5,6 | 5A, 6A | 5B, 6B | 5C, 6C |
<- OUTPUT
1 | 1A | 1B | 1C |
2 | 2A | 2B | 2C |
3 | 3A | 3B | 3C |
4 | 4A | 4B | 4C |
5 | 5A | 5B | 5C |
6 | 6A | 6B | 6C |
Any ideas or suggestions?
Thanks.
Hi @namerup
Check with this:
// Create a temporary table to hold the maximum number of comma-separated values
TempTable:
LOAD
Max(Len(ID) - Len(Replace(ID, ',', ''))) as MaxCommas
RESIDENT InputTable;
LET vMaxCommas = Peek('MaxCommas', 0, 'TempTable') + 1;
DROP TABLE TempTable;
// Create the output table by splitting the comma-separated values
OutputTable:
LOAD
SubField(ID, ',', IterNo()) as ID,
SubField(ColA, ',', IterNo()) as ColA,
SubField(ColB, ',', IterNo()) as ColB,
SubField(ColC, ',', IterNo()) as ColC
RESIDENT InputTable
WHILE IterNo() <= vMaxCommas;
Check Subfield()
Hello
Using Talend ETL have a look to tNormalize component
https://www.tutorialgateway.org/talend-tnormalize/
"TNormalise" works for a single comma separated column. But in a multiple comma separated column?
Hi @namerup
Check with this:
// Create a temporary table to hold the maximum number of comma-separated values
TempTable:
LOAD
Max(Len(ID) - Len(Replace(ID, ',', ''))) as MaxCommas
RESIDENT InputTable;
LET vMaxCommas = Peek('MaxCommas', 0, 'TempTable') + 1;
DROP TABLE TempTable;
// Create the output table by splitting the comma-separated values
OutputTable:
LOAD
SubField(ID, ',', IterNo()) as ID,
SubField(ColA, ',', IterNo()) as ColA,
SubField(ColB, ',', IterNo()) as ColB,
SubField(ColC, ',', IterNo()) as ColC
RESIDENT InputTable
WHILE IterNo() <= vMaxCommas;
Thanks.