Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Splitting a field into multiple fields

Hi dear QV Community

I have a field with information separated by '|', I need to split that into individual fields, as I can do that, can someone help

Original field value:

_XXX_|MATERIAL|OBRA CIVIL CONTRATADA|MATERIAL|MATERIAL|MATERIAL|MATERIAL|MATERIAL|MATERIAL|MATERIAL|MATERIAL|MATERIAL|MATERIAL|MATERIAL|MATERIAL|MATERIAL

Desired division

F1F2F3F4F5F6F7F8F9F10F11F12F13F14F15F16
_XXX_MATERIALOBRA CIVIL CONTRATADAMATERIALMATERIALMATERIALMATERIALMATERIALMATERIALMATERIALMATERIALMATERIALMATERIALMATERIALMATERIALMATERIAL

thanks

1 Solution

Accepted Solutions
MayilVahanan

Hi

Try like this

Tab2:

LOAD *,SubStringCount(Change1,'|') as test Inline [

Team1,Change1

A,10|20|30|40|50

];

for i=1 to 5

Tab23:

LOAD *, SubField(Change1,'|',$(i)) as fieldname$(i)

Resident Tab2 ;

NEXT i;

Hope that give idea for your requirement

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

7 Replies
MayilVahanan

Hi

Try like this

Load * ,SubField(FieldName,'|') as FieldName from tableName;

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

This option split in files not in fields

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

subfield(Fieldname, '|', 1) as F1,

subfield(Fieldname, '|', 2) as F2,

etc

-Rob

Not applicable
Author

Rob  thanks for ur help i know this option but not work for me because could vary the amount of fields

MayilVahanan

Hi

Try like this

Tab2:

LOAD *,SubStringCount(Change1,'|') as test Inline [

Team1,Change1

A,10|20|30|40|50

];

for i=1 to 5

Tab23:

LOAD *, SubField(Change1,'|',$(i)) as fieldname$(i)

Resident Tab2 ;

NEXT i;

Hope that give idea for your requirement

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

Thanks for ur help

Not applicable
Author

I have a similar problem. There is one column which contains comma separated values like A1,B2,C1 and I need to split this column into many columns and put the corresponding value there by its type (there is a separate value-type table like A1 is of type T1, B2 is of type T5..)

The resulting table should of the following structure:

A1 is in T1 column, B2 is in T5 column.

How can I add columns whose names are types, something like the result of the SQL statement "select Type from Types" shoud be additional column names to be added to the data table.

Number of records in the Types table is unknown, the number of comma separated values in the field is unknown.

Any ideas?