Unrolling nested list with index

[wanderingcatto]

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)

[pedrobergo]

Hi @wanderingcatto 

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, 

• Reference1-1 is correlated to Report1
• Reference1-2 is correlated to Report1
• Reference2-1 is correlated to Report2
• Reference2-2 is correlated to Report2

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

[wanderingcatto]

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

[wanderingcatto]

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.

[Saravanan_Desingh · Accepted solution]

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;

[wanderingcatto]

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.

[vinieme12 · Accepted solution]

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}}"
];