Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Create a hierarchy drill-down application

Hi,

I'm relatively new to qlik sense, and I'm tasked with creating an application that will allow the user to see the hierarchy of people directly rattached to him, but with numbers aggregated within all the children of this hierarchy.

To simplify, I've tried creating a minimal example with a sample hierarchy. The hierarchy is defined as follow:

sample hierarchy.png

I've generated a simple load script that loads this data into a hierarchy model for unbalanced n-level hierarchy. (took from this document).

Here's the script:

Organization:
LOAD * INLINE [
  User_Id,name,managerId
  1,Beverly Heath,0
  2,Tate Ochoa,1
  3,Velma Finley,1
  4,Calvin Parker,3
  5,Maxine Medina,3
  6,Wylie Robinson,1
  7,Mercedes Love,2
  8,Beau Rivers,6
  9,Wanda Kelly,2
  10,Buckminster Shelton,4
  ];

[Nodes]:
Hierarchy(User_Id, Manager_Id, Node, NodeParent, Node, NodePath, '/', NodeDepth) load
User_Id,
managerId as Manager_Id,
name as Node
resident Organization;

[TreeBridge]:
HierarchyBelongsTo (User_Id, Manager_Id, NodeName, TreeId, TreeName)
LOAD User_Id,
  managerId as Manager_Id,
     name as NodeName
Resident Organization;

[Trees]:
Hierarchy(TreeId, TreeParentId, Tree)
Load User_Id as TreeId,
  managerId as TreeParentId,
     name as Tree
Resident Organization;

Now, the issue is, I'm trying to, for now, add it to a "cross table" in order to have the drill-down behavior, and simply counting a distinct of the User_Id. I'm adding Node1-4 as the dimension, and remove null values for all.

It seem to work at first, as you can see:

table.PNG

But once I start opening a few more path of the hierarchy, it "breaks" and most of the data is deleted, only showing one branch of the hierarchy tree.

table broken.PNG

I'm not sure whether this is a bug or something that I'm doing that is utterly wrong?

Thanks

4 Replies
Highlighted
Partner
Partner

Re: Create a hierarchy drill-down application

That behaviour is caused when you suppress the null values because when a column has a null value the whole row is suppressed. Take Node 4 for instance, it's all nulls except for Buckminster Shelton so when you expand all the way to node 4 all the other rows are suppressed leaving you with one (the tree for Shelton).

I would say this is WAD on pivot tables.

Cheers

Highlighted
Not applicable

Re: Create a hierarchy drill-down application

Thanks for the help.

I understand that this could be a wanted behavior of the pivot table, but then, what would be the way to do what I want to do? Should I use another type of graph?

Highlighted
Master II
Master II

Re: Create a hierarchy drill-down application

Hi Xavier,

You can make use of drill down dimension to achieve what you are looking for. In the master dimension you will be able to specify the hierarchical order and this will help you drill down.

Thanks,

Sangram.

Highlighted
Creator III
Creator III

Re: Create a hierarchy drill-down application

Hi ! 🙂 

I allow myself to revive the subject.

I have the same problem 

ReddysWhat do you mean by "use the top down dimension"?

 

Thanks 🙂