Join leaf nodes with respective parent nodes in qlikview

Question

So I managed to get two node tables in which in one of them, I have the "tree" or structure of the nodes, and in the other table I have the last nodes of that table (Leaf nodes).

I would like to make a full table where I have in Column 1, the Distinct Leaf node Values and in Column 2, have one-on-one, all the parents that this node can have. 

So imagine I have this structure of nodes:

enter image description here

 As you can see, the leaf nodes are CM1011, CM1012, CM1021 and CM1022. So I want to have a table which would be something like this:

enter image description here

As you can see, the leaf nodes all are joined with their parent nodes. These are my tables:

Tree nodes (structure):

enter image description here

Leaf nodes:

enter image description here

May you help me with this problem?

Thanks regardless.

Answer 1

Function needed to do that is HierarchyBelongsTo https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptPrefixes/HierarchyBelongsTo.htm

We need to specify node and ancestor column, it require also name value which maybe is not needed in your case. You can use some other value from your dataset or as in example some dummy value - here row number. Additionally it will return also rows with node=node so we can filter it by rejecting DepthDiff which equals 0.

[nodes]:
HierarchyBelongsTo 
    (node, sub_node, row, sub_node, AncestorName, DepthDiff ) 
LOAD 
    *,
    RowNo() as row
Inline
[node, sub_node
CM1, CM1011
CM1011, CM10111
CM10111, CM101111];

    [nodes_cleared]:
LOAD
    node, 
    sub_node
Resident
     [nodes]
Where
    DepthDiff > 0;
         
    DROP Table [nodes];

Here is result table with example data: