# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Contributor III

## 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

1 Solution

Accepted Solutions
Honored Contributor

## Re: Executing of values till leaf node.

Try this:

```

input:
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:
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)
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:
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:
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;
```
18 Replies

## 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

talk is cheap, supply exceeds demand
Contributor III

## 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

## Re: Executing of values till leaf node.

talk is cheap, supply exceeds demand
Honored Contributor

## Re: Executing of values till leaf node.

This will generate your code table

```nodemap:
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;
Input
A
B
C
D
];

```
Highlighted
Contributor III

## Re: Executing of values till leaf node.

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

Honored Contributor

## Re: Executing of values till leaf node.

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

Honored Contributor

## Re: Executing of values till leaf node.

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

```

input:
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)
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:
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:
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;
```
Contributor III