topic Re: Comma delimited multi list to rows in Talend Studio

Comma delimited multi list to rows

namerup — Thu, 06 Jun 2024 06:13:06 GMT

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.

Re: Comma delimited multi list to rows

tresB — Thu, 06 Jun 2024 06:24:31 GMT

Check Subfield()

Re: Comma delimited multi list to rows

jeoste — Thu, 06 Jun 2024 07:21:28 GMT

Hello
Using Talend ETL have a look to tNormalize component
https://www.tutorialgateway.org/talend-tnormalize/

Re: Comma delimited multi list to rows

namerup — Thu, 06 Jun 2024 07:30:01 GMT

"TNormalise" works for a single comma separated column. But in a multiple comma separated column?

Re: Comma delimited multi list to rows

TauseefKhan — Thu, 06 Jun 2024 08:38:39 GMT

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;

Re: Comma delimited multi list to rows

namerup — Thu, 06 Jun 2024 09:12:26 GMT

Thanks.