topic Re: Executing of values till leaf node. in QlikView

Executing of values till leaf node.

ahmar811 — Fri, 23 Dec 2016 11:47:58 GMT

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

Re: Executing of values till leaf node.

Gysbert_Wassenaar — Fri, 23 Dec 2016 11:54:55 GMT

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.

ahmar811 — Fri, 23 Dec 2016 12:20:56 GMT

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.

Gysbert_Wassenaar — Fri, 23 Dec 2016 12:32:08 GMT

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.

adamdavi3s — Fri, 23 Dec 2016 13:06:39 GMT

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.

ahmar811 — Sat, 24 Dec 2016 04:22:06 GMT

hi adam,

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

nodemap:

Stem,Leaf

A,A1
B,B1
B,B2
C,C1
D,D1
B1,B2
C1,C2
D1,D2
C2,C3
D2,D3
];

Output for Stem B like below using applymap

Stem,Leaf

B,B1

appreciate your help

Re: Executing of values till leaf node.

adamdavi3s — Tue, 03 Jan 2017 07:26:42 GMT

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

Re: Executing of values till leaf node.

adamdavi3s — Tue, 03 Jan 2017 08:57:37 GMT

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.

ahmar811 — Tue, 03 Jan 2017 09:20:27 GMT

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.

adamdavi3s — Tue, 03 Jan 2017 09:33:09 GMT

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.

adamdavi3s — Tue, 03 Jan 2017 09:37:12 GMT

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.

ahmar811 — Tue, 03 Jan 2017 09:47:44 GMT

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.

adamdavi3s — Tue, 03 Jan 2017 09:58:04 GMT

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.

ahmar811 — Tue, 03 Jan 2017 10:14:31 GMT

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.

adamdavi3s — Tue, 03 Jan 2017 10:18:31 GMT

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

Re: Executing of values till leaf node.

ahmar811 — Tue, 03 Jan 2017 10:50:14 GMT

when we click on C value = 80 not 170.

Re: Executing of values till leaf node.

adamdavi3s — Tue, 03 Jan 2017 10:53:54 GMT

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

Re: Executing of values till leaf node.

ahmar811 — Tue, 03 Jan 2017 11:07:19 GMT

Yes

Re: Executing of values till leaf node.

adamdavi3s — Tue, 03 Jan 2017 11:43:21 GMT

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