Qlik Community

Ask a Question

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
Henric_Cronström

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

41 Comments
olac
Contributor III
Contributor III

Thank you for elaborating on this topic Hic, I have been struggling with this as well before deciding to go for SQL functions (CTE).

So letting go of HierarchyBelongsTo but rather use Hierarchy and then grouping on the path may be the solution. Impressive as usual.

As a future development suggestion I think I still agree with Roland, would be nice to have it all calculated in a tree level that supports non dimensional hierarchies (or branching out upwards in the hierarchy if you like), like common table expressions in sql.


Ola

5,986 Views
Not applicable

Henric, thanks for this great article. It comes right on time because I'm going to rework my assets datafile containing installations and components in an unbalanced hierarchy. If I would have studied the hierarchyBelongsTo a little bit sooner I could have saved myself a lot of trouble. Creating the 'trees' table is a great idea.

0 Likes
5,986 Views
Not applicable

Thanks Henric, for this great article. It comes right on time because while struggling with an unbalanced hierarchy. Creating the 'trees' table is a great idea. Would you have a sample demo app to share so we get an idea on scripting end on how to accomplish the 'trees' table.

Again thanks for sharing this idea, it was very helpful.

Thanks,

DD.

0 Likes
5,986 Views
Henric_Cronström

A script example can be found on http://community.qlik.com/docs/DOC-5334

HIC

0 Likes
5,986 Views
Not applicable

Hi Henric,

Thanks for sharing the code link. Would be able to share how you can build the Districts pivot table with tree view which expands and collapses as shown by you at the top, that would be of great help to better understand the article.

Appreciate all your help.

Happy Thanks Giving!

Thanks,

DD

0 Likes
5,986 Views
Henric_Cronström

The pivot table is easy to make:

  • Create a pivot table with Node1..Node7 as dimensions.
  • Make the dimension columns narrower.
  • On Properties - Style, check the "Indent Mode"
  • Also check the "Use only first dimension label"
  • Uncheck "Vertical Dimension Cell Borders"
  • On Properties - Presentation, check the "Subtotals on Top"

Done!

HIC

0 Likes
5,986 Views