topic Re: Separating string content in a single column into multiple columns, dynamically in Talend Studio

Separating string content in a single column into multiple columns, dynamically

RMotta2408 — Fri, 07 Jun 2024 13:59:43 GMT

Hello everyone.

How do I extract a string content from a column into multiple columns?

I have this:

And I want to turn it into this:

Mind you, the original column "TEAMS" may have a different number of values. So the Job has to be dynamic to create as many columns as the values in the original "TEAMS" column.
The "-" character is the values separator.

I just can't seem to get over this hurdle.
Can anyone please help me?

Thank you so much,

Rui

Re: Separating string content in a single column into multiple columns, dynamically

hic — Fri, 07 Jun 2024 21:17:49 GMT

Use Subfield(). See https://community.qlik.com/t5/Design/Loops-in-the-Script/ba-p/1473543

If you want the result in different columns, you will need to index the subfield function, like

Subfield(Teams,'-',1) as C1,
Subfield(Teams,'-',2) as C2,
Subfield(Teams,'-',3) as C3,
etc...

But usually, it is better to omit the third parameter and get the result in rows.

Re: Separating string content in a single column into multiple columns, dynamically

rwunderlich — Tue, 11 Jun 2024 12:38:35 GMT

I find it's usually more useful to get the Teams in rows as @hic suggested.

SubField(TEAMS, '-') as Team

However, if you want the exact structure you posted -- Team_1, Team_2, etc, you can get that field structure for an arbitrary number of teams using Generic Load:

Data:
LOAD * Inline [
NAME, SURNAME, ID, TEAMS
Rui, Manuel, 35, Lakers-Spurs-Desportivo
Alphonse, Arrigo, 98, Lakers-Pelicans
];
Teams:
Generic LOAD 
 ID,
 'Teams_' & AutoNumber(Team, ID),
 Team
;
Load 
 ID,
 SubField(TEAMS, '-') as Team
Resident Data;

For more see https://qlikviewcookbook.com/2010/05/use-cases-for-generic-load/

** I just realized this question was for Talend, my answer was for Qlik Sense! **

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: Separating string content in a single column into multiple columns, dynamically

Xiaodi_Shi — Tue, 11 Jun 2024 07:44:10 GMT

Hello,

Here is a built-in component tExtractDelimitedFields in talend product which generates multiple columns from a delimited string column.

https://help.talend.com/en-US/components/8.0/processing/textractdelimitedfields

For more information, please have a look at my demo job screenshots.

Fee

Best regards

Sabrina

Re: Separating string content in a single column into multiple columns, dynamically

RMotta2408 — Tue, 11 Jun 2024 11:46:38 GMT

Hi Sabrina,

The problem with this solution is that I do not know how many columns are in column TEAMS.
As I said, the original column "TEAMS" may have a different number of values. So the Job has to be dynamic to create as many columns as the values in the original "TEAMS" column.

Thank you,

Rui

Re: Separating string content in a single column into multiple columns, dynamically

RMotta2408 — Tue, 11 Jun 2024 11:48:00 GMT

Thank you.

I will have a look into it.
I appreciate it.

Rui

Re: Separating string content in a single column into multiple columns, dynamically

RMotta2408 — Tue, 11 Jun 2024 11:48:58 GMT

Thank you,

I will give it a try.

Thak you so much,

Rui

Re: Separating string content in a single column into multiple columns, dynamically

Shicong_Hong — Wed, 12 Jun 2024 02:03:57 GMT

@RMotta2408

First, use tNormalize to generate multiple rows.

Then, convert rows to columns using tPivotToColumnsDelimited.

Create a new out.csv file:

name;surname;id;team1;team2;team3
Rui;Manuel;35;value1;value2;value3

If you need to output the result to an excel file, read the csv file using Dynamic schema and output an excel file.

Regards

Shicong

Re: Separating string content in a single column into multiple columns, dynamically

RMotta2408 — Wed, 12 Jun 2024 09:44:33 GMT

Hi @Shicong_Hong ,

Thank you for your reply.
But for me to use the tNormalize component, don't I have to know the schema beforehand?

My problem is the original column "TEAMS" may have a different number of values. So the Job has to be dynamic to create as many columns as the values in the original "TEAMS" column.

In this specific case, I have to create 3 more columns. But I may have to create more (or less) columns depending on the values the column "TEAMS" contain.

Thanks,

Rui

Re: Separating string content in a single column into multiple columns, dynamically

RMotta2408 — Wed, 12 Jun 2024 09:59:01 GMT

Hi everyone,

First off, I want to thank each and everyone of you who came forward and try to help me.

Maybe I should approach my problem differently. I have this string:

"aaaa;bbbb;cccc;dddd;eeee"

How do I transform this into columns?

Keep in mind that the only thing I'm sure of is that only the first 3 values ("aaaa", "bbbb" and "cccc") are constant. The number of values from that point forward may vary. So I don't know, beforehand, how many columns I will have at the end.

Thanks,

Rui

Re: Separating string content in a single column into multiple columns, dynamically

Shicong_Hong — Wed, 12 Jun 2024 10:06:34 GMT

see the below image, this expression create the columns based on the number of values in "Teams".

"team"+Numeric.sequence(row3.id,1,1)