Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Split the value of single field into multiple columns

Hi,

I have a following table:

EmpID, Val

100, a/b

200, e/f/g

300, h/i/j/k/l

I want to split the Column 'Val' into Multiple columns to give the following output:

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

100, a/b, a, b, -, -, -

200, e/f/g, e,f,g, -, -

300, h/i/j/k/l, h, i, j, k, l

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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

t2:

load * inline

[

EmpID, Val

100, a/b

200, e/f/g

300, h/i/j/k/l

];

tmp: load max(len(KeepChar(Val, '/'))) as numslash Resident t2;

let numslash=Peek('numslash');

TRACE $(numslash);

DROP Table tmp;

set str = '';

FOR i=1 to $(numslash)+1

  set c1=SubField(Val, '/', $(i));

  set c11=Split$(i);

  set str=$(str) $(c1) as $(c11) , ;

  TRACE $(str);

NEXT i;

final:

NoConcatenate load

  $(str)

  EmpID, Val

Resident t2;

DROP Table t2;

View solution in original post

3 Replies
er_mohit
Master II
Master II

see the attached file

maxgro
MVP
MVP

t2:

load * inline

[

EmpID, Val

100, a/b

200, e/f/g

300, h/i/j/k/l

];

tmp: load max(len(KeepChar(Val, '/'))) as numslash Resident t2;

let numslash=Peek('numslash');

TRACE $(numslash);

DROP Table tmp;

set str = '';

FOR i=1 to $(numslash)+1

  set c1=SubField(Val, '/', $(i));

  set c11=Split$(i);

  set str=$(str) $(c1) as $(c11) , ;

  TRACE $(str);

NEXT i;

final:

NoConcatenate load

  $(str)

  EmpID, Val

Resident t2;

DROP Table t2;

Not applicable
Author

Thanks Massimo...