18 Replies Latest reply: Jan 3, 2017 6:43 AM by Adam Davies

# Executing of values till leaf node.

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

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

Thanks & Regards

Ahmar

• ###### Re: Executing of values till leaf node.

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

• ###### Re: Executing of values till leaf node.

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

• ###### Re: Executing of values till leaf node.

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

• ###### Re: Executing of values till leaf node.

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
];

```
• ###### Re: Executing of values till leaf node.

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

• ###### Re: Executing of values till leaf node.

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

• ###### Re: Executing of values till leaf node.

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;
```
• ###### Re: Executing of values till leaf node.

hi Adam,

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

Your logic solved my 99% problem.

• ###### Re: Executing of values till leaf node.

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

• ###### Re: Executing of values till leaf node.

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;
```
• ###### Re: Executing of values till leaf node.

Hi Adam,

It is possible to load all values i.e Value1, Value2...... on single field like Value.

• ###### Re: Executing of values till leaf node.

I don't quite understand, do you mean the SUM of all values? so a single total value for the row?

• ###### Re: Executing of values till leaf node.

we want to get the coresponding value for all the layers as in Data table

in a single column

• ###### Re: Executing of values till leaf node.

OK so in your example for your row C value = 170?

• ###### Re: Executing of values till leaf node.

when we click on  C value = 80 not 170.

• ###### Re: Executing of values till leaf node.

Ok so the value column should show the value for the lowest leaf in the row?

• ###### Re: Executing of values till leaf node.

Yes

• ###### Re: Executing of values till leaf node.

Ok I will have a think when I get a moment, it must be pretty easy to do as you just add a column called 'lowestleaf' and join that to the value table