topic Re: Hierarchy List in QlikView

Hierarchy List

amit_shetty78 — Tue, 10 Dec 2013 17:57:44 GMT

Hi all,

I am working on hierarchical data which has up to 6 levels and these hierarchies are coming along with the transaction data itself. So there is no separate master for it.

My data set I am trying to load is:

User Dept L1 Dept L2 Dept L3 Dept L4 Dept L5 Dept L6.

I would like to show the department list as hierarchies with the expandable '+'/'-' icons.

I have tried to concatenate the 6 level column as Dept L1/Dept L2/Dept L3/Dept L4/Dept L5/Dept L6 and then in the list box used the separator to show as hierarchy.

The selections work fine for the lowest level where I can select it but when I try to select an higher level which has multiple child levels, the selection does not work.

Any advice on how I can get that to work.

thanks,

Amit.

Re: Hierarchy List

amit_shetty78 — Thu, 12 Dec 2013 10:41:53 GMT

Hi All,

Any suggestions/advice on how to resolve this please.

thanks.

Re: Hierarchy List

Anonymous — Thu, 12 Dec 2013 10:46:18 GMT

Amit

Have you tried a Chart of type Pivot Table ?

Best Regards, Bill

Re: Hierarchy List

amit_shetty78 — Thu, 12 Dec 2013 14:40:45 GMT

Hi Bill,

Thank you for reverting back.

I have used that but that is not what I am looking for. I need a hierarchy shown in the list and the user to be able to select any level in the hierarchy and the data for the chosen and the levels under them be used to show metrics in on the dashboard.

Best,

Amit.

Re: Re: Hierarchy List

michael_maeuser — Thu, 12 Dec 2013 15:08:41 GMT

Hi, example attached works

Re: Hierarchy List

hic — Thu, 12 Dec 2013 18:01:43 GMT

If you want to select nodes on higher levels, these must have corresponding records in the data. For instance if you load the following data using a path defined as Level1 & '/' & Level2:

Level1, Level2

Unit1, Department1

Unit1, Department2

you will be able to display this in a tree view list box. But you will not be able to select 'Unit1'. However, if you instead load

Level1, Level2

Unit1,

Unit1, Department1

Unit1, Department2

then it will work the way you want.

HIC

Re: Hierarchy List

amit_shetty78 — Wed, 15 Jan 2014 12:21:20 GMT

Hi Henric Cronström

Thank you for the help so far.

Would it be possible to provide me a sample qvw with my scenario working please.

I an not able to make it to work. The hierarchy list is only selectable on the lowest node.

I have data in 2 table with the following fields. I have atached an excel with some sample data.

Can we use this to come up with a hierarchy list select-able at every node?

HierarchyTable1:

User

Hierarchy1

Hierarchy2

Hierarchy3

Hierarchy4

Hierarchy5

Hierarchy6

Hierarchy7

Hierarchy8

Hierarchy9

Hierarchy10

Hierarchy_Complete

HierarchyTable2:

User

Hierarchy

NodeNumber

Appreciate your help on this.

Thanks,

Amit.

Re: Hierarchy List

Wed, 15 Jan 2014 12:57:07 GMT

Hi,

As Henric said you need to link the upper levels to basic data.

I published a document to explain that: http://community.qlik.com/docs/DOC-4823

Fabrice

Re: Re: Hierarchy List

hic — Thu, 16 Jan 2014 12:13:37 GMT

You can create an Adjacent Nodes table using a For-Next loop, looping through the columns of Excel sheet. This you can use in a Hierarchy statement.

Then you need to assign your users - define which node they belong to. They are leaves. I did this using an inline, but you should do it somewhere in your DB.

Once you have done this, you can create your tree-view list box.

Below you can find my script:

HIC

For vLevel = 1 to 10
Let vParentLevel = vLevel - 1 ;
Let vParent = If(vLevel=1,'Null()','Hierarchy$(vParentLevel)') ;
AdjacentNodes:
Load * Where Not IsNull(Node);
Load If(Node<>'NULL',Node) as Node,
      If(ParentNode<>'NULL',ParentNode) as ParentNode;
Load
      Hierarchy$(vLevel) as Node,
      $(vParent) as ParentNode
FROM hierarchy.xls (biff, embedded labels, table is HierarchyTable1$);

Next vLevel

Nodes:
Hierarchy (NodeID, ParentID, NodeName, ParentName, NodeName, Path, '/', Depth)
Load
      Node as NodeID,
      Node as NodeName,
      ParentNode as ParentID
      Resident AdjacentNodes;
Drop Table AdjacentNodes;

Leaves:
Load * inline
"User, NodeName
Adam, Global Store [L8]
David, Open Operations [L10]";

Re: Re: Hierarchy List

amit_shetty78 — Thu, 23 Jan 2014 10:46:16 GMT

Thanks Henric Cronström

I was able to follow your document and configure my data as used in your example. Seems to be working for me now.

thank you for the assistance.

Amit.

Re: Hierarchy List

amit_shetty78 — Thu, 23 Jan 2014 10:47:06 GMT

Thanks AUNEZ FABRICE I used the document and seems to be working for me now.