Dynamically Splitting fields

[Sloane]

Hello everyone, 

I am quite new to qlik and would appreciate if anyone has any useful tip on how to achieve dynamically splitting columns into multiple columns based on delimeters.

here is a sample of what I have:

and I am trying to achieve this:

Any help would be greatly appreciated. 

[marcus_sommer]

You could use subfield() with just two parameters to split the values into separate records. To create from them separate fields you may use a generic load - but within many scenarios it's better not to create such artificial crosstables else just keeping the information within a single field and using a pivot within the UI to show the horizontally information.

[Sloane]

Thanks Marcus, I tried the subfield function and that split into rows which is not entirely bad but it gets tricky when I need the matching split scores for each subject. 

[marcus_sommer]

Maybe it's better to control the subfield() explicitly which makes it easier to split multiple fields at the same time and provides also a counter. This might be done with something like this:

load subfield(Score & ',' , ',', iterno()) as Score, iterno() as IterNo
from Source while iterno() <= substringcount(Score & ',', ',');

[Sloane]

Thank you Marcus.

Surprisingly, I have used this exact method and it sadly didnt work. From your suggestion though, I see that you are splitting only the Score but I would ideally like the subjects to be split at the same time

[MartW · Accepted solution]

@Sloane ,

can you try this script?

Table:
Load 
	*,
	len(KeepChar(Score,','))		as Check
;
LOAD
    ID,
    Score,
    Subject
FROM [lib://tmp/tmp.xlsx]
(ooxml, embedded labels, table is Sheet1);


Check:
NoConcatenate
Load max(Check) as max Resident Table;
drop field Check from Table;

let vMax = peek('max',0,'Check')+1;
Trace >>>$(vMax);

for vCounter = 1 to $(vMax)
	left join(Table)
    LOAD
        ID,
        subfield(Score,',',$(vCounter)) as Score$(vCounter),
        subfield(Subject,',',$(vCounter)) as Subject$(vCounter)
    FROM [lib://tmp/tmp.xlsx]
    (ooxml, embedded labels, table is Sheet1);
next

drop table Check;
let vMax = null();
let vCounter = null();

I've tested this with the data you showed in the screenshot and that worked.

[Sloane]

Thanks a lot @MartW !!

this definitely helped a lot!