Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My data contains nested list (to reduce file size). Below is a sample for illustration:
Category | ReportList | ReferenceNestedList |
CatA | [Report1, Report2, Report3] | [[Reference1-1, Reference1-2], [Reference2-1, Reference2-2, Reference2-3], [Reference3-1, Reference3-2]] |
CatB | [Report4, Report5] | [[Reference4-1, Reference4-2, Reference4-3], [Reference5-1]] |
I want to unroll the nested list and create a table chart within my qlik sense app that looks like this instead:
Category | Reports | References |
CatA | Report1 | Reference1-1 |
CatA | Report1 | Reference1-2 |
CatA | Report2 | Reference2-1 |
CatA | Report2 | Reference2-2 |
CatA | Report2 | Reference2-3 |
CatA | Report3 | Reference3-1 |
CatA | Report3 | Reference3-2 |
CatB | Report4 | Reference4-1 |
CatB | Report4 | Reference4-2 |
CatB | Report4 | Reference4-3 |
CatB | Report5 | Reference5-1 |
While I've been successful in using TextBetween() and SubField() to get the items within the list and pivot them, how do I arrange it such that all my items within ReferenceNestedList[0] gets mapped besides ReportList[0], items within ReferenceNestedList[1] gets mapped besides ReportList[1], so on and so forth?
(The attached qvf shows my attempt and result thus far)
Try something like this. For simplicity I have changed '[' to '{'.
tab1:
LOAD Category,
TextBetween(ReportList,'{','}') As ReportList,
TextBetween(ReferenceNestedList,'{{','}}') As ReferenceNestedList
INLINE [
Category, ReportList, ReferenceNestedList
CatA, "{Report1, Report2, Report3}", "{{Reference1-1, Reference1-2}, {Reference2-1, Reference2-2, Reference2-3}, {Reference3-1, Reference3-2}}"
CatB, "{Report4, Report5}", "{{Reference4-1, Reference4-2, Reference4-3}, {Reference5-1}}"
];
Left Join(tab1)
LOAD Category, ReportList, PurgeChar(SubField(ReportList,',',IterNo()),'{} ') As Reports, IterNo() As K1
Resident tab1
While IterNo()<=SubStringCount(ReportList, ',')+1
;
Left Join(tab1)
LOAD Category, ReportList, ReferenceNestedList, SubField(ReferenceNestedList,'}',IterNo()) As tmpReferences, IterNo() As K1
Resident tab1
While IterNo()<=SubStringCount(ReferenceNestedList, '}')+1
;
Left Join(tab1)
LOAD Category, ReportList, ReferenceNestedList, tmpReferences, K1, PurgeChar(SubField(tmpReferences,',',IterNo()),'{} ') As References
Resident tab1
While IterNo()<=SubStringCount(tmpReferences, ',')+1
;
tab2:
NoConcatenate
LOAD Category, Reports, References
Resident tab1
Where References <> ''
;
Drop Table tab1;
here is an alternate script, this can be done in two passes
temp:
Load
Category
,Subfield(ReportList,',',iterno()) as Reports
,subfield(TextBetween(ReferenceNestedList, '{','}', iterno()),',') as Reference
while iterno()<=NoOfLists
;
load Category
,Textbetween(ReportList,'{','}') as ReportList
,replace(replace(ReferenceNestedList,'{{','{'),'}}','}') as ReferenceNestedList
,SubStringCount(ReportList,',')+1 as NoOfLists
inline [
Category,ReportList,ReferenceNestedList
CatA,"{Report1, Report2, Report3}","{{Reference1-1, Reference1-2}, {Reference2-1, Reference2-2, Reference2-3}, {Reference3-1, Reference3-2}}"
CatB,"{Report4, Report5}","{{Reference4-1, Reference4-2, Reference4-3}, {Reference5-1}}"
];
Please give more details about relationship between ReportsList and ReferenceNestedList
Category | ReportList | ReferenceNestedList |
CatA | [Report1, Report2, Report3] | [[Reference1-1, Reference1-2], [Reference2-1, Reference2-2, Reference2-3], [Reference3-1, Reference3-2]] |
So,
And so on...
If my assumption is correct the following code will create a new table (FinalTable) with Unique Values from Report and Reference
FinalTable:
Load Category,
SubField(PurgeChar(ReportList,'[]'),',') as ReportList_Unique
Resident Sample;
Tmp:
Left Join (FinalTable)
Load Category,
SubField(Replace(ReferenceNestedList_Unique,'Reference','Report'),'-',1) as ReportList_Unique,
ReferenceNestedList_Unique;
Load Category,
SubField(PurgeChar(ReferenceNestedList,'[]'),',') as ReferenceNestedList_Unique
Resident Sample;
[],
Pedro
Yes, you're right. Items within ReferenceNestedList[0] maps to ReportList[0], items within ReferenceNestedList[1] maps to ReportList[1], so on and so forth
Thanks @pedrobergo. However, I do need the sorting to be done based on index rather than by the name of the value. The data that I've provided is just a made-up sample to make it easier to understand what I need.
My ReportList could be [Report1, Report2, Report3] and ReferenceNestedList could be [[AAA, BBB], [CCC, DDD, EEE], [FFF, GGG]] - and I'd still need the AAA and BBB to be mapped to Report1.
Try something like this. For simplicity I have changed '[' to '{'.
tab1:
LOAD Category,
TextBetween(ReportList,'{','}') As ReportList,
TextBetween(ReferenceNestedList,'{{','}}') As ReferenceNestedList
INLINE [
Category, ReportList, ReferenceNestedList
CatA, "{Report1, Report2, Report3}", "{{Reference1-1, Reference1-2}, {Reference2-1, Reference2-2, Reference2-3}, {Reference3-1, Reference3-2}}"
CatB, "{Report4, Report5}", "{{Reference4-1, Reference4-2, Reference4-3}, {Reference5-1}}"
];
Left Join(tab1)
LOAD Category, ReportList, PurgeChar(SubField(ReportList,',',IterNo()),'{} ') As Reports, IterNo() As K1
Resident tab1
While IterNo()<=SubStringCount(ReportList, ',')+1
;
Left Join(tab1)
LOAD Category, ReportList, ReferenceNestedList, SubField(ReferenceNestedList,'}',IterNo()) As tmpReferences, IterNo() As K1
Resident tab1
While IterNo()<=SubStringCount(ReferenceNestedList, '}')+1
;
Left Join(tab1)
LOAD Category, ReportList, ReferenceNestedList, tmpReferences, K1, PurgeChar(SubField(tmpReferences,',',IterNo()),'{} ') As References
Resident tab1
While IterNo()<=SubStringCount(tmpReferences, ',')+1
;
tab2:
NoConcatenate
LOAD Category, Reports, References
Resident tab1
Where References <> ''
;
Drop Table tab1;
Thank you! This appears to work. Not sure what's happening within the syntax for now but I'll slowly break it apart to figure it out.
here is an alternate script, this can be done in two passes
temp:
Load
Category
,Subfield(ReportList,',',iterno()) as Reports
,subfield(TextBetween(ReferenceNestedList, '{','}', iterno()),',') as Reference
while iterno()<=NoOfLists
;
load Category
,Textbetween(ReportList,'{','}') as ReportList
,replace(replace(ReferenceNestedList,'{{','{'),'}}','}') as ReferenceNestedList
,SubStringCount(ReportList,',')+1 as NoOfLists
inline [
Category,ReportList,ReferenceNestedList
CatA,"{Report1, Report2, Report3}","{{Reference1-1, Reference1-2}, {Reference2-1, Reference2-2, Reference2-3}, {Reference3-1, Reference3-2}}"
CatB,"{Report4, Report5}","{{Reference4-1, Reference4-2, Reference4-3}, {Reference5-1}}"
];