topic Re: Split a field using 2 delimiters in QlikView

Split a field using 2 delimiters

Tue, 12 Aug 2014 07:45:48 GMT

Hi,

I have a following table:

EmpID, Val

100, a-1/b-2

200, e-3/f-4/g-5

I want to split the Column 'Val' using delimiters '-' and '/' into Multiple columns to give the following output:

EmpID, Val, Split1, Split2, Split3, Split4, Split5, Split6

100, a-1/b-2, a, 1, b, 2

200, e-3/f-4/g-5, e,3,f, 4, g, 5

Here, the new columns added, Split*, are dynamic as I'm not sure of the number of splits that are available in each column value.

Can someone please help? Thanks in advance.

marcus_sommer — Tue, 12 Aug 2014 10:26:49 GMT

Try this:

t1:

Load EmpID, Val, subfield(replace(Val, '-', '/'), '/') as ValSplit From xyz;

Subfield() will create for each value a (new) row. This is a stream-output and not a crosstable-output but often this is a better solution.

- Marcus

Tue, 12 Aug 2014 14:38:46 GMT

Awesome!!! Thanks, Marcus. This was so simple