How can I turn one field of data into multiple fields?

[#1]

One field of my data looks like this:

0001,0002,0003

All the other fields have just one entry.

I would like to turn this field into 3 different fields because we need to drill down into those item numbers. Thanks for the help.

[#2]

You can create directly a field in a chart withSubfield

=subfield(@1,',',2) extract the second word from the field @1using ',' as separator

or you can also store new fields in the script as ;

LOAD @1,

subfield(@1 , ',' , 1) as Word1 ,

subfield(@1 , ',' , 2) as Word2
subfield(@1 , ',' , 3) as Word3 FROM

(txt, codepage is 1252, explicit labels, delimiter is '\t', msq);

If you want to count how many "words" between each delimiter you can use SUBSTRINGCOUNT

substringcount(@1,',')+1. If this number is a constant, it's easy to create new fields in the script. Otherwise, it's more complicated.

JJ

[#3]

Thanks for your resonse. I chose the 2nd method you mentioned, loading them as subfields, and it did separate them out which is awesome. 2nd problem I'm running into now is Is there any way to make it so instead of being Word1 Word2 and Word3, they would all be Word1? Thanks.

[#4]

Try this

LOAD @1
FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq, filters(
Unwrap(Col, Pos(Top, 2)),
Unwrap(Col, Pos(Top, 2))
))
WHERE(@1 <> '');

zzz is your file with only one field

If you have more than N rows, repeat Unwrap(Col, Pos(Top, 2)) N-1 time

JJ

[johnw · Accepted solution]

You can extract multiple rows with subfield(). Just leave off the final parameter. For instance:

subfield(YourMultiEntryField,',') as YourSingleEntryField

QlikView will automatically duplicate the rest of the data, creating one row per subfield. From the help text:

"In its two-parameter version, the subfield function generates one record for each substring that can be taken from a larger string s with delimeter 'delimeter'. If several subfield functions are used in the same load statement, the Cartesian product of all combinations will be generated."

[#6]

Thanks John,

your solution is excellent.

JJ

[#7]

This worked really good. Thanks. Hopefully this is the last issue, a couple of records contain some spaces. Is there anyway to remove these spaces but leave the rest of the data in the record intact?

[johnw]

Remove ALL spaces? Probably purgechar(...,' ').

[Abhisheksingh1]

What if there are 70 values seperated by a delimeter , I cant keep using subfield 70 times , can we create any looping in that case ?

[Todd66]

Try it!

1. Select the cell or column that contains the text you want to split.
2. Select Data > Text to Columns.
3. In the Convert Text to Columns Wizard, select Delimited > Next.
4. Select the Delimiters for your data. ...
5. Select Next.
6. Select the Destination in your worksheet which is where you want the split data to appear.
7. Mynordstrom Workday