Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Splitting a field into multiple fields

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

7 Replies

Re: Splitting a field into multiple fields

Hi

Try like this

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

Hope it helps

Not applicable

Re: Splitting a field into multiple fields

This option split in files not in fields

Re: Splitting a field into multiple fields

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

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

etc

-Rob

Not applicable

Re: Splitting a field into multiple fields

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

Re: Splitting a field into multiple fields

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

Not applicable

Re: Splitting a field into multiple fields

Thanks for ur help

Not applicable

Re: Splitting a field into multiple fields

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?

Community Browser