Hierarchy List

[amit_shetty78]

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.

[amit_shetty78]

Hi All,

Any suggestions/advice on how to resolve this please.

thanks.

[Anonymous]

Amit

Have you tried a Chart of type Pivot Table ?

Best Regards,     Bill

[amit_shetty78]

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.

[michael_maeuser]

Hi, example attached works

[hic]

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

[amit_shetty78]

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.

[#8]

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

[hic]

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]";

[amit_shetty78]

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.