A different approach to hierarchies with respect to the tree-view List Box

[#1]

I had been struggling for quite some time to find a hierarchy data model that works well within QlikView.

In particular, I want to be able to use the Tree-View in the List Box, and ensure that when a non-leaf node is selected, that the parent behave as we're used to showing distinctions between: children selected; all possible children selected; and all children selected.

If you generate your tree-view paths using the "hierarchy" load script command, you will find that selecting a parent on its own doesn't work very well, and selecting a parent (with children) results in only showing that some children have been selected, with the row coloured in grey and a little green dot at the far right.

This shortcoming is further exacerbated if you use the AJAX ZFC since all the list boxes must be the width of the longest path. QlikView will create a horizontal scroll bar, but it's pain to have to scroll both horizontally in order to see what has been selcted.

There is a solution to this problem though which I've yet to see posted in the QV Community Forum. The key to making the tree-view work properly in QlikView is by performing a "Reflexive Transitive Closure" operation on your hierarchy to produce "Closure" records. This goes one step beyond "flattening" that QV's hierarchy currently does. I'll provide a simple example below to show you what I mean.

Let's say I have a simple hierachy containing only two levels. And to keep it simple, there are only three rows: A parent and two children.

Here is the schema and data:

ParentId, MemberId, Name

[NULL], 1, "Mother"

1, 2, "Son"

1, 3, "Daughter"

If I use QlikView's hierarchy loader, I will get a new table that will look like this:

MemberId, Name1, Name2, Path

1, "Mother", [NULL], "Mother"

2, "Mother", "Son", "Mother/Son"

3, "Mother", "Daughter", "Mother/Daughter"

The problem is, in my fact table I don't have any rows which link directly back to MemberId 1.

But if I perform a Reflexive Transitive Closure, I instead get a table that looks like this:

MemberId, Name1, Name2, Path, Level

2, "Mother", "Son", "Mother", 1

2, "Mother", "Son", "Mother/Son", 2

3, "Mother", "Daughter", "Mother", 1

3, "Mother", "Daughter", "Mother/Daughter", 2

So in effect, I'm making a copy of every leaf node for each level I go up the hierarchy. This is why when I just click on "Mother", I am implicitly selecting MemberIds 2 and 3, which are both linked to my fact table (and all other dimensions).

As corollary I've noticed a significant reduction in my binary footprint (e.g. I went from 350 MB down to 300 MB) by changing all my hierarchies into Closures. Furthermore, I believe there may also be a performance improvement, although I haven't a proper benchmarking exercise. Furthermore, by adding a "Level" field, it is quite easy to determine the highest or lowest level selected. This too can come in handy.

As for performing a Reflexive Transitive Closure. Well, you'll probably have an easier time doing this using a different ETL tool (e.g. Pentaho DI includes a Closure step), or perhaps using Common Table Expressions in SQL. It would be nice to see an improved hierarchy function in QV itself.

[#2]

Very interesting! I've had clients decide against using tree view listboxes for exactly this reason (going with a drill-down multibox structure instead).

So, is the only addition to your HIERARCHY() function the explicit addition of the level field name?

Thanks,

DJ

[#3]

The built-in hierarchy loader function won't create this closure for you. You will need to do this on your own before the data is loaded.

Some ETL tools (e.g. Pentaho Data Integration) have an explicit CLOSURE step to do this for you.

In my particular case I just flattened the hierarchy using a Common Table Expression and then looped through the leaf records, once for each level, INSERTing proxy leaf records for each level.

I must say it was a pain to develop this code (for any arbitrary hierarchy), but now that it works it's easy for me to load geographical hierarchies that play well with the List Box Tree view.

Another enhancement I discovered is to eliminate the horizontal scroll bar (this makes a big difference on the AJAX client), and create a TRIGGER to auto select all children if a parent path is selected, to avoid any confusion on what's being selected.

The end result is a more intuitive user experience.

[#4]

What about using the native HierarchyBelongsTo() QlikView function to build that table?

[#5]

I looked into using the HierarchyBelongsTo when I originally set out to solve this problem. I've revisited this function. Indeed it does create a Closure (although not exactly the same kind of Closure we need).

While it is possible to use HierarchyBelongsTo to create the closure and then associate it with the output of the Hierarchy function (which will create the required path field, and the member fields for each level), the problem is that HierarchyBelongsTo generates explicit rows for non-leaf members.

For example, if I have a geographical Org hierarchy, made up of two retail locations which belong to a single city. HierarchyBelongsTo will generate 5 rows, with 3 of the rows associated with the city.

In actual fact, we only want 4 rows, with only 2 rows associated with the city.

In other words, we just want to flatten the hierarchy, fetch the leaf nodes, and loop for each level, generating parent rows with the appropriate parent path.

Let me know if you need more explanation.

[Anonymous]

nhepburn wrote:

I must say it was a pain to develop this code (for any arbitrary hierarchy),

Hmmm... Does anybody have a piece of QlikView code which loads the reflexive closure table, either from an adjacent nodes table or an expanded nodes table using resident loading?

(If not then yes, I will give it a try....   )

[Anonymous]

Problem solved, and here's what I did, in case the next guy or girl may find it useful:

1) Start with an adjacent nodes table. Include an extra field which marks a node as a leaf or not, for example "IsLeaf" or whatever you like.

2) Perform a hierarchy load and include the IsLeaf field, resulting in an expanded nodes table.

3) Reload only the leaves from the expanded nodes table into a new table, using resident load where IsLeaf = 1. (You will probably need to rename fields or use qualify, in order to actually create a new table.)

In my case i was lucky, since all my leaves were at level 3, so I could do:

4) Load the leaves once again, but delete everything after and including the last forward slash in the path string. (Use, for instance the subfield command here)

and finally

5) Load the leaves, deleting everything after and including the second to last '/'.

...and presto, we're done.

For a hierarchy with leaves at different levels it gets a little more complicated, but hopefully you get the picture.

[andy]

The solution might be easier than you think.

When using tree-view listboxes I always

1. check the "Suppress horizontal scroll bar" on the presentation-tab

2. Tell the users that if you want every possible leaf selected you have to collapse the node first.

I think this is a correct behaviour of Qlikview. In some way you have to be able to select the leaves on an intermediate node only and this is what is done if you select the node when its leafs are expanded.

/Andy

[#9]

So the treeview listbox behave differently when the nodes are expende or collapsed?

It is not very much the same like other analysis products (i.e excell pivot table) but if it is function well, so i guess it is OK.

[Anonymous]

There is also another way how to select hierarchy members:

1) create Drill down group using

2) New Sheet Object-> ListBox using newly created Drill-down Group (you have to use  New Sheet object as Select Field does not show groups)

and Voila - selection via non-leaf levels works as expected (although the interface is not as nice as TreeView - as clumsy as whole Drill-down group concept, but hey, given QV is not able to fix even trivial defects for couple of years, at least it works).