Executing of values till leaf node.

[ahmar811]

Dear Experts,

This are my input tables

Input Table:

Stem	Leaf
A	A1
B	B1
C	C1
D	D1
B1	B2
C1	C2
D1	D2
C2	C3
D2	D3

I have given to analyse for Stem (A,B,C,D)

Step 1- I need to find Leaf value of Stem (A,B,C,D)  i.e (A1,B1,C1,D1)

Step 2- Find these values in Stem & corresponding leaf values i.e (B2,C2,D2)

Note- If corresponding value is not found  in Step 2 like for A1 then its null

Above steps should be performed till all leaf are found in loop with first iteration as Level2 then Level 3 & so on.

Data Table:

Leaf	Value
A1	10
B1	20
C1	30
D1	40
B2	50
C2	60
D2	70
C3	80
D3	90

The Result should be look like this, so that we will be able to make a drill group with all levels & get corresponding values as shown:

  Output :

Level0	Level1	Value	Level2	Value	Level3	Value
A	A1	10	-	-	-	-
B	B1	-	B2	50	-	-
C	C1	-	C2	-	C3	80
D	D1	-	D2	-	D3	90

'I have tried using left join to make Level but the logic failed at Level 2.

Also I m not able to retain null values as in above Note'

Thanks & Regards

Ahmar

[Gysbert_Wassenaar]

You can use the hierarchy function to create a table with that layout. See this blog post: Unbalanced, n-level hierarchies

[ahmar811]

hi gysbert

I have read the blog post but it already has a required data.

In my case I need to make the data

appreciate your help

[Gysbert_Wassenaar]

Sorry, I thought you said you already had an input table. I can't help you generate that if you don't.

[adamdavi3s]

This will generate your code table

nodemap:
Mapping load * inline [
Stem,Leaf
A,A1
B,B1
C,C1
D,D1
B1,B2
C1,C2
D1,D2
C2,C3
D2,D3
];


Hierarchy:
load Level0, Level1,Level2, applymap('nodemap',Level2,null()) as Level3;
load Level0, Level1, applymap('nodemap',Level1,null()) as Level2;
load Input as Level0, applymap('nodemap',Input,null()) as Level1;
LOAD * inline [
Input
A
B
C
D
];

[ahmar811]

hi adam,

your logic was good but when one stem and more than one leaf like Stem B on below table

          nodemap:

        Stem,Leaf

1. A,A1 
2. B,B1
3. B,B2
4. C,C1 
5. D,D1 
6. B1,B2 
7. C1,C2 
8. D1,D2 
9. C2,C3 
10. D2,D3 
11. ];

Output for Stem B like below using applymap

Stem,Leaf

B,B1

appreciate your help

[adamdavi3s]

OK so your data is NOT a one for one map as per your original example?

[adamdavi3s]

OK give this a crack, I am sure this could be done much more neatly though

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;

[ahmar811]

hi Adam,

Your logic was perfect for creating layers but we also want corresponding values.

Your logic solved my 99% problem.

[adamdavi3s]

Sorry yes I see that in the second part of the post. That should be easy to add