Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

Hierarchies are very common in all database and business intelligence solutions. Usually they are balanced and with a fix number of levels, and then they do not pose any problems. Just load the data, add a drill-down group, and you’re done.


Adjacent Notes source 2.png

But there is one type of hierarchy that is somewhat tricky to get right – an unbalanced, n-level hierarchy. Typical for this type of hierarchy is that the levels are not named, and you really don’t know on which level you need to search for a specific node.

 

Usually such a hierarchy is stored in an Adjacent Nodes table, i.e. a table that has one record per node and each node has a reference to its parent.

 

Such a table can be loaded into QlikView directly using the Hierarchy prefix. This prefix will transform the Adjacent Nodes table into an Expanded Nodes table that has additional fields that you can use in your app.

 

Data model single table - BP.png

 

With the fields in this table, you can easily create a pivot table and a tree-view list box. Below you can see some wine districts displayed in both these object types:

 

Tables - BP.png

 

One challenge with hierarchies is that you can refer to a node in two different ways: Either to the node including the entire sub-tree, or to the node only, excluding all sub-nodes. In the example with the wine districts, it would mean any wine from Bordeaux, and unspecified Bordeaux, respectively. In the pivot table above, the difference is obvious: Any wine from Bordeaux sums up to 150 units, and the unspecified Bordeaux sums up to 18 units.

 

A user usually wants to make selections referring to the entire sub-tree, but the above solution does not have any field for this. To create such a field, you need the second hierarchy-resolving prefix – the HierarchyBelongsTo.

 

This prefix will also transform the hierarchy table. The result will be a table containing one record per descendant-ancestor pair. In other words, the ancestor (tree ID) will link to all its descendants (node ID), and can thus be used to make selections of entire sub-trees. (The “TreeBridge” table in the picture below.)

 

But it doesn’t stop here… The above solution creates one field in which tree searches can be made, but in order to create a drill-down for trees, you need an additional table – an expanded nodes table for the trees. This can be created with a second Hierarchy statement, but now one that links to the tree ID instead of the node ID. (The “Trees” table in the picture below.)

 

Data model full - BP.png

 

The data model with the three hierarchy tables is the one I recommend: It generates all fields you need.

 

A more elaborate explanation with script examples can be found in the technical brief about Hierarchies.

 

HIC

 

Further reading related to this topic:

Authorization using a Hierarchy

Bill of Materials

44 Comments
matthewjbryant
Creator II
Creator II

Did you get round to this blog post Henric Cronström?I have a complicated hierarchy of parts and component parts that I'm trying to model. The techniques discussed here look promising, but don't get me where I need to be, sadly.

Great post though.

0 Likes
6,195 Views
hic
Former Employee
Former Employee

Usually, you want the hierarchy resolution (the hierarchy prefix) in the data only, and a Section access that just lists nodes and who is allowed to see them (=no hierarchy prefix). See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/25/hierarchy-authorization

HIC

6,195 Views
hic
Former Employee
Former Employee

If you test it and conclude that it works, then it works. There is not just one correct way to solve a problem.

It is not possible to attach a file in a blog comment. Open a discussion topic and attach it there.

HIC

0 Likes
6,195 Views
hic
Former Employee
Former Employee

Just put my name in it, like Henric Cronström. (Type @ followed by henric and you will get a list of names.)

HIC

0 Likes
6,195 Views
Anonymous
Not applicable

Amigo Henric

Teria como postar como fazer uma tabela com grafico, com o calculando o percentual comparativo ano?

Adoro todo os seus POST, aprendo a cada dia com você e todos da community mais ainda tenho muito a aprender.

Agradeço .

0 Likes
6,165 Views
agni_gold
Specialist III
Specialist III

Can you please help me on my problem ..... please

https://community.qlik.com/thread/163345

0 Likes
6,165 Views
avastani
Partner - Creator III
Partner - Creator III

Try this

https://community.qlik.com/servlet/JiveServlet/download/5334-4-81955/Hierarchies.pdf

See if HierarchyBelongsTo helps you.

-afv.

Tel: 646.773.7936

0 Likes
6,165 Views
Not applicable

Nice explanation

but why is there any need to create hierarchy with trees table.?

can't we use nodes to create tree drill down ?

0 Likes
2,652 Views
hic
Former Employee
Former Employee

No, because the nodes in the Nodes table only refer to the node itself and not the sub-nodes.

If you take the node Bordeaux in the wine example, the node only refers to wines that are labelled "Bordeaux" and nothing else. But it does not include the sub-nodes inside the Bordeaux district. In the pivot table in the picture the Sum(Amount) is 18 for the node "Bordeaux" and 150 for the tree "Bordeaux".

But if a user clicks on "Bordeaux", he/she would want to see the entire sub-tree - including also the sub-districts. So, you need the Trees table to create this relationship.

HIC

0 Likes
2,652 Views
qlikviewwizard
Master II
Master II

Hi HIC, Nice document.

Thank you for sharing.

2,652 Views