table with cascade link

DarkTemplar — Tue, 05 Mar 2024 12:41:22 GMT

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.

Re: table with cascade link

LRuCelver — Tue, 05 Mar 2024 14:48:57 GMT

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;

topic table with cascade link in App Development

table with cascade link

Re: table with cascade link