Creating a hierarchy from a flat many to one structure

This is a quick answer to the post here:

https://community.qlik.com/message/1183485

The question was, with an input of type [parent,child] where there could be many children to one parent, is it possible to create a cross-table style output to put each layer in a new column.
The trick here is to work out the maximum depth of the hierarchy and we can then simply rejoin the input to itself in order to create the additional columns:

 
input:
load * inline [
Stem,Leaf
A,A1
B,B1
B,B2
C,C1
D,D1
B1,B2
B1,B3
C1,C2
D1,D2
C2,C3
D2,D3
D3,D4
D4,D5
D4,D6
];
 
//cheat using the hierarchy belongs to to sus out our layers
HierarchyBelongsTo:
HierarchyBelongsTo (Node, Parent, NodeName, AncestorID, AncestorName, DepthDiff)
Load 
	Leaf as Node ,
	Leaf as NodeName,
	Stem as Parent
resident input;
 
//create a layers table to we can figure out which layer we want to load 
LEFT JOIN (input)
LOAD DISTINCT Node as Leaf, max(DepthDiff)+1 as MaxDepth 
Resident HierarchyBelongsTo
GROUP BY Node;
 
//figure out our max depth we need to work to
MaxLayers:
LOAD max(MaxDepth) as MaxLayers
resident input;
 
LET vLayers = peek('MaxLayers');
 
//drop working table
DROP TABLE MaxLayers;
 
 
//LOAD our top layer which won't have a record in the layers table
HierarchyMain:
LOAD DISTINCT Stem as Layer0 
resident input
WHERE NOT EXISTS (Node,Stem);
 
//now run our loop
for i=1 to $(vLayers)
let x = $(i)-1;
LEFT JOIN (HierarchyMain) 
LOAD Stem as 'Layer$(x)',Leaf as 'Layer$(i)'
resident input
WHERE MaxDepth = $(i) ;
next i;
 
//drop working table
DROP TABLE HierarchyBelongsTo;
DROP TABLE input;

Leave a Reply