table with cascade link

[DarkTemplar]

Hello,

I am looking for a method to establish links between main reference information and sub-references in a system using a single table. These links are often cascading. Although I've considered using a recursive function, I haven't found any information on how to do this in Qlik. Do you have a solution to suggest?

sous_reference	reference
ref01002	ref01001
ref01003	ref01002
ref01004	ref01003
ref02002	ref02001
ref02003	ref02002
ref02004	ref02002
ref03002	ref03001
-	ref04001

Desired result

sous_reference	reference
ref01004	ref01001
ref02003	ref02001
ref02004	ref02001
ref03002	ref03001
-	ref04001

Thank you for your answer.

[LRuCelver · Accepted solution]

You could probably use a Hierarchy for this, but this also works:

Data:
NoConcatenate Load * Inline [
sous_reference, reference
ref01002, ref01001
ref01003, ref01002
ref01004, ref01003
ref02002, ref02001
ref02003, ref02002
ref02004, ref02002
ref03002, ref03001
-, ref04001
];

Links:
NoConcatenate Load
	sous_reference,
    reference,
    1 as continue
Resident Data
Where not Exists(reference, sous_reference);

Let vContinue = 1;

Do while vContinue = 1
	Rename Table Links to Links_Old;
    
    Links:
    NoConcatenate Load
    	sous_reference,
        If(IsNull(next_reference), reference, next_reference) as reference,
        RangeMax(If(IsNull(next_reference), 0, 1)) as continue;
	Load        
    	sous_reference,
        reference,
        If(continue = 1, Lookup('reference', 'sous_reference', reference, 'Data')) as next_reference
    Resident Links_Old;
    
    Continue:
    NoConcatenate Load
    	Max(continue) as continue
    Resident Links;
    
    Let vContinue = Peek('continue', 0, 'Continue');
    
    Drop Tables Links_Old, Continue;
Loop
Let vContinue;
Drop Table Data;
Drop Field continue;