Try this:
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
];
left join (input)
values:
load * inline [
Leaf, Value
A1, 10
B1, 20
C1, 30
D1, 40
B2, 50
C2, 60
D2, 70
C3, 80
D3, 90
];
//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)', Value as 'Value$(i)'
resident input
WHERE MaxDepth = $(i) ;
next i;
//drop working table
DROP TABLE HierarchyBelongsTo;
DROP TABLE input;
