Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

ahmar811
Contributor III

Executing of values till leaf node.

Dear Experts,

This are my input tables

Input Table:

StemLeaf
AA1
BB1
CC1
DD1
B1B2
C1C2
D1D2
C2C3
D2D3

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:

LeafValue
A110
B120
C130
D140
B250
C260
D270
C380
D390

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 :

Level0Level1ValueLevel2ValueLevel3Value
AA110----
BB1-B250--
CC1-C2-C380
DD1-D2-D390

'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

1 Solution

Accepted Solutions
adamdavi3s
Honored Contributor

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;

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

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.


talk is cheap, supply exceeds demand
adamdavi3s
Honored Contributor

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

];

Highlighted
ahmar811
Contributor III

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

adamdavi3s
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?

adamdavi3s
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:

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
Contributor III

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.

adamdavi3s
Honored Contributor

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

Community Browser