Splitting a field into multiple fields

[#1]

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

F1

F2

F3

F4

F5

F6

F7

F8

F9

F10

F11

F12

F13

F14

F15

F16

_XXX_

MATERIAL

OBRA CIVIL CONTRATADA

MATERIAL

MATERIAL

MATERIAL

MATERIAL

MATERIAL

MATERIAL

MATERIAL

MATERIAL

MATERIAL

MATERIAL

MATERIAL

MATERIAL

MATERIAL

thanks

[MayilVahanan]

Hi

Try like this

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

Hope it helps

[#3]

This option split in files not in fields

[rwunderlich]

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

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

etc

-Rob

[#5]

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

[MayilVahanan · Accepted solution]

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]

Thanks for ur help

[#8]

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?