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

[RMotta2408]

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

[hic]

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.

[rwunderlich]

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

[Xiaodi_Shi]

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

 

[RMotta2408]

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

[RMotta2408]

Thank you.

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

Rui

[RMotta2408]

Thank you,

I will give it a try.

Thak you so much,

Rui

[Shicong_Hong]

@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

[RMotta2408]

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

[RMotta2408]

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