Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
see the attached file
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;
Thanks Massimo...