topic Re: Splitting a field into multiple fields in QlikView

Splitting a field into multiple fields

Wed, 10 Oct 2012 15:22:14 GMT

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:

Desired division

F10

F11

F12

F13

F14

F15

F16

_XXX_

MATERIAL

OBRA CIVIL CONTRATADA

MATERIAL

thanks

Re: Splitting a field into multiple fields

MayilVahanan — Wed, 10 Oct 2012 15:30:42 GMT

Try like this

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

Hope it helps

Re: Splitting a field into multiple fields

Wed, 10 Oct 2012 15:54:07 GMT

This option split in files not in fields

Re: Splitting a field into multiple fields

rwunderlich — Wed, 10 Oct 2012 16:01:59 GMT

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

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

etc

-Rob

Re: Splitting a field into multiple fields

Wed, 10 Oct 2012 16:11:37 GMT

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

MayilVahanan — Wed, 10 Oct 2012 17:01:24 GMT

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

Re: Splitting a field into multiple fields

Wed, 10 Oct 2012 18:44:23 GMT

Thanks for ur help

Re: Splitting a field into multiple fields

Tue, 30 Oct 2012 10:39:53 GMT

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?