Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
vvvvvvizard
Specialist
Specialist

Hierarchy belongs to or Hierarchy

This is my Load statement ,  i want to turn it into a Hierachy 

It is actually is a  cost centre hierarchy from top to bottom every field

Top Segment l7

Seglevel 6 

Segement level 5 

ect

This is my load ID  , i dont have node id 1 and parent id but i know which fields need are associated with thiose ID's

LOAD
CostCentre,
CostCentre_Desc,
BUSUB,
BUSUB_Desc,
BU,
BU_Desc,
Company_Div,
Company_Div_Desc,
Segm_L1,
Segm_L1_Desc,
Segm_L2,
Segm_L2_Desc,
Segm_L3,
Segm_L3_Desc,
Segm_L4,
Segm_L4_Desc,
Segm_L5,
Segm_L5_Desc,
Segm_L6,
Segm_L6_Desc,
Segm_L7,
Segm_L7_Desc
FROM [lib://xxxxxxxxxxxxxxx ]

(xxxxxxxxxxxxxxxxxxxxxxxxx);

Labels (1)
1 Solution

Accepted Solutions
Henric_Cronström

What you have is an expanded nodes table, and I interpret your question as if you want to create an adjacent nodes table. If this is the case, you will need to do something like the following:

You say that Segment_L7 is the top, and if so the following should do the trick (assuming that all Segm_LX are unique):

// =====
Load Segm_L7 as ID, Segm_L7_Desc as Desc, Null() as ParentID From Lib ...;
Load Segm_L6 as ID, Segm_L6_Desc as Desc, Segm_L7 as ParentID From Lib ...;
Load Segm_L5 as ID, Segm_L5_Desc as Desc, Segm_L6 as ParentID From Lib ...;
Load Segm_L4 as ID, Segm_L4_Desc as Desc, Segm_L5 as ParentID From Lib ...;
Load Segm_L3 as ID, Segm_L3_Desc as Desc, Segm_L4 as ParentID From Lib ...;
Load Segm_L2 as ID, Segm_L2_Desc as Desc, Segm_L3 as ParentID From Lib ...;
Load Segm_L1 as ID, Segm_L1_Desc as Desc, Segm_L2 as ParentID From Lib ...;
// =====

How the BU, CostCentre and Company fit into this hierarchy, I don't know, since I haven't seen the data.

See also white paper on https://community.qlik.com/t5/QlikView-Documents/Hierarchies/ta-p/1487801

Good luck!

HIC

View solution in original post

6 Replies
vvvvvvizard
Specialist
Specialist
Author

Guess to complex for the community this a challenge lol 

vvvvvvizard
Specialist
Specialist
Author

@Henric_Cronström  Your article did not cover how to do this , please give guidance

Henric_Cronström

What you have is an expanded nodes table, and I interpret your question as if you want to create an adjacent nodes table. If this is the case, you will need to do something like the following:

You say that Segment_L7 is the top, and if so the following should do the trick (assuming that all Segm_LX are unique):

// =====
Load Segm_L7 as ID, Segm_L7_Desc as Desc, Null() as ParentID From Lib ...;
Load Segm_L6 as ID, Segm_L6_Desc as Desc, Segm_L7 as ParentID From Lib ...;
Load Segm_L5 as ID, Segm_L5_Desc as Desc, Segm_L6 as ParentID From Lib ...;
Load Segm_L4 as ID, Segm_L4_Desc as Desc, Segm_L5 as ParentID From Lib ...;
Load Segm_L3 as ID, Segm_L3_Desc as Desc, Segm_L4 as ParentID From Lib ...;
Load Segm_L2 as ID, Segm_L2_Desc as Desc, Segm_L3 as ParentID From Lib ...;
Load Segm_L1 as ID, Segm_L1_Desc as Desc, Segm_L2 as ParentID From Lib ...;
// =====

How the BU, CostCentre and Company fit into this hierarchy, I don't know, since I haven't seen the data.

See also white paper on https://community.qlik.com/t5/QlikView-Documents/Hierarchies/ta-p/1487801

Good luck!

HIC

vvvvvvizard
Specialist
Specialist
Author

Much appreciated , il try it with just two nodes and mark your answer as correct once implemented

vvvvvvizard
Specialist
Specialist
Author

Costcentre is another tables which has the amounts , how do i add ID'S to that table sorry to trouble 

eg

Other_table:

Cost centre 

OurCoutryCurrencyAmt 

from ...........................

Henric_Cronström

I need to see the data to be able to answer properly. There could be problems to find the correct cost centre when the mother node has a different cost centre than the daughter.

The following could perhaps work:
// =====
Load Segm_L7 as ID, CostCentre From Lib ... Where IsNull(Segm_L6_Desc);
Load Segm_L6 as ID, CostCentre From Lib ... Where IsNull(Segm_L5_Desc);
Load Segm_L5 as ID, CostCentre From Lib ... Where IsNull(Segm_L4_Desc);
Load Segm_L4 as ID, CostCentre From Lib ... Where IsNull(Segm_L3_Desc);
Load Segm_L3 as ID, CostCentre From Lib ... Where IsNull(Segm_L2_Desc);
Load Segm_L2 as ID, CostCentre From Lib ... Where IsNull(Segm_L1_Desc);
Load Segm_L1 as ID, CostCentre From Lib ... ;
// =====

HIC