article Hierarchies in Member Articles

Hierarchies

hic — Mon, 11 Nov 2013 14:07:46 GMT

Hierarchies are an important part of all business intelligence solutions, used to describe dimensions that naturally contain different levels of granularity. Some are simple and intuitive whereas others are complex and demand a lot of thinking to be modeled correctly.

What is the difference between a balanced and an unbalanced hierarchy?
How can a hierarchy be loaded into QlikView?
Which data model should be used?
How do I create an authorization scheme using a hierarchy?
How can I check for data integrity problems in the hierarchy data?

These questions and others are answered in this Technical Brief. Attached you will also find a sample unbalanced, n-level hierarchy along with the script to load it.

Re: Hierarchies

Tue, 12 Nov 2013 10:06:56 GMT

Henric, you're a wizard!

Re: Hierarchies

Tue, 12 Nov 2013 14:18:22 GMT

Thank you for the qlikview hierarchy compendium - especially the trick with the expanded nodes table to describe the trees!

Is there a possibility to show the first level of the drill-down group (Tree1...Tree7)?

In case of an hierarchy there is only one top level (ex. The World) and therefore the second level (Africa, Americas,...) is shown straight away in a dirll-down group used in charts. From point of user interaction it would be great to see every single level and drill down manually. There seems to be the possibility to add a NULL value (see Rob Wunderlich in http://community.qlik.com/message/333452#333452) but in case of a hierarchy this don't seem appropriate to me.

Re: Hierarchies

hic — Tue, 12 Nov 2013 15:34:04 GMT

There are two ways you can do this:

Define your group as a Cyclic group instead of a Drill-down group. Then you will need to drill down manually all the time.
Use Rob's suggestion: e.g. add
Concatenate Load null() as TreeID AutoGenerate 1;
after the Expanded Trees' table.

HIC

Re: Hierarchies

Marcio_Campestrini — Fri, 21 Mar 2014 10:52:02 GMT

Henric, thank you form another great post. I've just needed this and now it's done.

Re: Hierarchies

Anonymous — Tue, 25 Mar 2014 00:34:05 GMT

Henric:

You're a step ahead.

Thanks.

CB.

Re: Hierarchies

adiarnon — Wed, 25 May 2016 07:04:48 GMT

Henric, thank you!!

I have a problem in my hierarchy-

i have my acount dimension-

ELEMENT	PARENT	WEIGHT
A		1
B	A	-1
C	A	1
D	C	1
E	C	-1

MY FACT

ACOUNT	VALUE
B	3
C	2
D	5
E	6

WHEN I NEED THE VALUE FOR C-(D AND E HIS SONS)

SUM(VALUE (E))* WEIGHT (OF THE CONNECTION BETWEEN E+C)+

SUM(VALUE (D))* WEIGHT (OF THE CONNECTION BETWEEN D+C)+

SUM(VALUE (C))

6*(-1)+

5*1 +

THATS THE CALCULATION THAT I NEED

I DONt KNOW HOW TO MAKE IT WORK?!

HOPE ITS CLEAR

ADI

Re: Hierarchies

Anonymous — Thu, 18 May 2017 23:09:06 GMT

I'm struggling with building an unbalanced hierarchy in qlik sense for my company's org structure. I want to the user to be able to select from a job title, e.g., field: VPA and user selects from names, which are also tied to fact table metrics, so when user selects that name, everyone under that VPA's numbers are included. My main HR table has an individual and manager's name & alias and individual's job title. When i use the name as the hierachy foundation, then all the names need to fit in the name1, name2, etc. bucket, which they don't given the unbalanced hierarchy (e.g., vpa might be level 1 or level 2). I thought to add a tree table but can't figure out what to use or how to build it out. I'm unable to open your wine district app as an example to get a better sense of how the tree table works. Thx for any guidance you can provide! Kara

Re: Hierarchies

kamalqlik — Mon, 08 Jan 2018 10:11:28 GMT

Nice Post Sir

Re: Hierarchies

Joaquin_Lazaro — Thu, 21 Mar 2019 15:02:38 GMT

Hello Henric:

Would you mind to have a look at this Post

I have a problem with a Hierarchy but anybody write me

Thanks in advance

Joaquín

Re: Hierarchies

prieper — Fri, 24 May 2019 10:17:29 GMT

Hi Henrik,

is there an elegant way to implement kind of "LEFT KEEP" within the hierarchy-creation, i.e. to show only those elements (and related hierarchy), for which values exist in the fact-table?

Peter

Re: Hierarchies

Brokenhips — Mon, 17 Jun 2019 13:52:08 GMT

Hi Henric, just back to Qlik after a number of absent years.

Struggling with heirarchies and could do with some advice/pointers if you have the time

I am trying to create a hierarchy for Make, model,derivative from the below structured data - having already added in the node information.

I am unsure of how this should be loded to produce it ?or even if I actually need to ?

Regards

Parent Node	Manufacturer	Snr Node	Model	Child Node	Derivative
1	ALFA ROMEO	100	GIULIETTA DIESEL HATCHBACK	300	1.6 JTDM-2 120 Tecnica 5dr
2	BMW	101	3 SERIES SALOON	301	330e M Sport 4dr Step Auto
2	BMW	102	5 SERIES DIESEL SALOON	302	520d [190] M Sport 4dr Step Auto
`	FORD	103	ECOSPORT DIESEL HATCHBACK	303	1.5 TDCi 95 Titanium 5dr [17in]
3	FORD	104	FIESTA DIESEL	304	1.5 TDCi Sport Van