Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
RMotta2408
Creator II
Creator II

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

Hello everyone.

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

I have this:

RMotta2408_0-1717768397438.png

 

And I want to turn it into this:

RMotta2408_1-1717768477428.png


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

 

Labels (2)
10 Replies
hic
Former Employee
Former Employee

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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;
rwunderlich_0-1717986568348.png

 

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! **

 
Xiaodi_Shi
Support
Support

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.

textractdelimitedfields_1.pngtextractdelimitedfields_2.pngtextractdelimitedfields_3.png

 

 

Fee

Best regards

Sabrina

 

 

 

 

RMotta2408
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Thank you.

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

Rui

RMotta2408
Creator II
Creator II
Author

Thank you,

I will give it a try.

Thak you so much,

Rui

Shicong_Hong
Support
Support

@RMotta2408 

First, use tNormalize to generate multiple rows.

Shicong_Hong_0-1718157528712.png

Shicong_Hong_1-1718157556794.png

Shicong_Hong_2-1718157598808.png

 

Then, convert rows to columns using tPivotToColumnsDelimited.

Shicong_Hong_3-1718157664859.png

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
Creator II
Creator II
Author

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.

RMotta2408_0-1718185310537.png


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
Creator II
Creator II
Author

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