topic Re: Separating single column into multiple columns based on value(s) in Talend Studio

Separating single column into multiple columns based on value(s)

ameliag — Fri, 19 Jul 2024 06:28:05 GMT

Hi, I'm looking at dividing a single column with values 2 and 3 (category ids) into two separate columns these are joined by a part id. So the columns I would want are Category_id_2, Category_id_3 & Part_id. I have been able to create two separate files with Category_id_2 & Part_id and another with Category_id_3 & Part_id however I cannot figure out how to join them into one!

Would anyone be able to assist?

Thanks,

Amelia

Re: Separating single column into multiple columns based on value(s)

BrunPierre — Fri, 19 Jul 2024 09:42:21 GMT

Provide data sample and the anticipated result.

Re: Separating single column into multiple columns based on value(s)

quentin-vigne — Mon, 22 Jul 2024 07:56:58 GMT

Hi ameliag,

Could you provide a sample of your data so that we know how the separation take place ?

In the meantime, you could look into tExtractDelimitedField which is a component that split a column into multiple columns if you give it a separator

https://help.talend.com/en-US/components/8.0/processing/textractdelimitedfields#:~:text=Generates%20multiple%20columns%20from%20a,names%20as%20the%20original%20ones.

Re: Separating single column into multiple columns based on value(s)

SterreKapteijns — Mon, 22 Jul 2024 08:29:37 GMT

Hi Amelia,

For me it is not entirely clear what you mean and how the field is formatted. If for example, the field is formatted as category_id_2 - part_id - category_id_3, you could retrieve the fields with left(), right(), substring() or index() to get the index of the '-' character.

Re: Separating single column into multiple columns based on value(s)

Tyrone2556 — Wed, 24 Jul 2024 13:29:23 GMT

Hi Amelia, if your field has a delimiter perhaps you could use the subfield function? I am not entirely sure what you mean, can you give us an example?

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunctions/SubField.htm