Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Thanks & Regards
Ahmar
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;
You can use the hierarchy function to create a table with that layout. See this blog post: Unbalanced, n-level hierarchies
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
Sorry, I thought you said you already had an input table. I can't help you generate that if you don't.
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
];
hi adam,
your logic was good but when one stem and more than one leaf like Stem B on below table
nodemap:
Stem,Leaf
Output for Stem B like below using applymap
Stem,Leaf
B,B1
appreciate your help
OK so your data is NOT a one for one map as per your original example?
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;
hi Adam,
Your logic was perfect for creating layers but we also want corresponding values.
Your logic solved my 99% problem.
Sorry yes I see that in the second part of the post. That should be easy to add