Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am working with a dataset where one variable has a number of variables within it. I need to parse through the text to extract each variable value.
Current:
Desired:
Thank you for any help you can provide!
Hi,
Your Extra column looks like JSON, if so have you seen the following thread;
Solved: Qlik Sense parsing of a json field - Qlik Community - 1795740
Cheers,
Chris.
Hi @maprinci ,
you can use this version :
Tmp:
load *, purgechar(subfield(newExtrat,':',1),'{}"') as Field,purgechar(subfield(newExtrat,':',2),'{}"') as Vuale;
load *,subfield(Extra,',') as newExtrat inline [
Customer;Group; Extra
1;A1;{"Field1":"A","Field2":"B","Field3":"C"}
2;A2;{"Field1":"D","Field2":"E","Field3":"F"}
3;A3;{"Field1":"A","Field2":"G","Field3":"D"}
](delimiter is ';');
left join load count(newExtrat) as Tm1 resident Tmp group by Customer;
CombinedGenericTable:
Load distinct Customer,"Group" resident Tmp;
DATA:
generic LOAD
Customer,
"Group",
Field,
Vuale
Resident Tmp;
Drop Table Tmp;
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
//trace $(i) - '$(vTable)';
IF WildMatch('$(vTable)', 'DATA.*') THEN
LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
output: