topic How to create Hierarchy from flat table structure in QlikView

How to create Hierarchy from flat table structure

Wed, 17 Feb 2010 20:17:04 GMT

Hierarchy offered by QlikView seems to work with the fashion of NodeID-ParentID.

We are using flat table structure to define hierarchies. eg -

Product_Level_5,Product_level_4,Product_level_3,Product_level_2,Product_level_1

How do I load this data in "hierarchy" fashion? I am curious to use Tree View List box in QV 9.

I thought QlikView can show data in tree fashion if a "group" is attached to a listbox... But looks like the data itself has to be defined in hierarchical way during the load script..

Appreciate any help.... Thanks!

How to create Hierarchy from flat table structure

pdumas — Wed, 17 Feb 2010 22:13:44 GMT

I suggest you just concatenate the fields with a "/" as separator

Then display the concatenated field in a list box

check the "show as treeview" property

Thats it

Pierre.

How to create Hierarchy from flat table structure

rwunderlich — Wed, 17 Feb 2010 22:48:18 GMT

The Tree View breaks up any field by a given delimiter. So given a Field named "Product" with contents
"Sports, Winter, Outdoor, Skis"

Product would could be placed in a List Box with the delimiter set to " , " and displayed as a Tree View.

You can build the value of Product any number of ways. Hierarchy statements are only one way.

-Rob

How to create Hierarchy from flat table structure

Fri, 19 Feb 2010 16:13:16 GMT

May be I didnt understand completely.. but if I define a list box using expression such as - ProdctLevel5 & ", " & ProdctLevel4 & ", " ProdctLevel3 and use ", " as a delimiter then it shows the tree however any selection made inside tree wont affect other charts...

also selection made on other charts/tables should make corresponding selection in the tree...

can you please explain what am I missing here?!

thanks for your help..

How to create Hierarchy from flat table structure

Tue, 23 Feb 2010 17:08:39 GMT

Bump!

How to create Hierarchy from flat table structure

rwunderlich — Tue, 23 Feb 2010 20:13:23 GMT

Building the list as an listbox expression won't link to anything. I thought your rows already had the "product1, product2, ..." as keys. Could you post a bit of sample data or a qvw so we can understand what your data looks like?

-Rob

How to create Hierarchy from flat table structure

Mon, 02 Aug 2010 21:36:55 GMT

Any suggestions? I agree with nrbhattad. Simply using a list box and checking show tree view doesn't work the way intend a hierachy to work. There has to be a different way!!!

How to create Hierarchy from flat table structure

wizardo — Mon, 02 Aug 2010 22:47:41 GMT

qlikview takes the fashion of NodeID-ParentID.and by the use of the hirarchy statement creates the same flat table you already have

in addition to that it creates a field that contains the concatenated values of each row's individual new column

(Product_Level_5,Product_level_4,Product_level_3,Product_level_2,Product_level_1) and puts a delimiter in-between like this

Product_Level_5/Product_level_4/Product_level_3/Product_level_2/Product_level_1 this new field (default name it path) is the field you use in the new treebox

what you can do if you already have the flat table is to create the field yourself and use it in the treebox and then you will get the selections you want.

the trick with using a concatenated expression in the treebox enables you to create hierarchies on the fly with out first creating them in the script

Mansyno

How to create Hierarchy from flat table structure

Mon, 09 Aug 2010 20:25:04 GMT

Thanks Mansyno for your reply. I've already tried what you're suggesting. Not to repeat myself, unfortunately it doesn't work the way I intend it to work. I can see the tree structure. The way it works using hierarchy load is when you click on the parent, all its children are selected. However, with you're suggestion I cannot make use of that ability. In fact, I cannot make any selection from the list box.

If there is another way, please suggest. Thanks

Vishal

How to create Hierarchy from flat table structure

wizardo — Mon, 09 Aug 2010 21:41:50 GMT

maybe i wasnt understood

i didnt mean to concat the values in an expression in the list box

i ment concat it already in the script

the it will be linked to the other records and you will be able to selct the lower levels

How to create Hierarchy from flat table structure

Tue, 10 Aug 2010 15:56:42 GMT

Maybe this should help

Please refer to the attached file. You should find a hierarchy table. I scripted what you've suggested. However, it doesn't accomplish what I'm expecting. In the example, I'm hoping that when I click on a continent like North America, I would expect everything under the umbrella of North America should be selected/associated. I don't seem to get that functionality. Why?

How to create Hierarchy from flat table structure

Thu, 09 Sep 2010 13:41:04 GMT

Hi,

you have to build a loop that you get that structur:

europe

europe/london

europe/london/roman

so for each level there has to be a row.

bye

Roman

How to create Hierarchy from flat table structure

suniljain — Thu, 09 Sep 2010 13:44:47 GMT

You have make excelsheet of excelsheet of hierarchy and connect it with data modelling .

Some ERP tool maintaining this type of hierarchy in that manner.

How to create Hierarchy from flat table structure

Thu, 09 Sep 2010 13:46:11 GMT

Like this:

How to create Hierarchy from flat table structure

Wed, 08 Dec 2010 05:14:28 GMT

Hi,

Today I came across the same problem as you have originally described, however mine was in relation to a customer hierarchy, not a product. In any case, the solution would be the same.

Basically what you need to do is rebuild the parent/child relationship so you can then load using the hierarchy load feature. To do this, I loop through all the possible levels of the hierarchy and create the parent/child relationship between each node. I use distinct to ensure I do not duplicate:


FOR iLevel = 1 TO 9;
 LET iChildLevel = $(iLevel) + 1;
 TEMP_HIER_TABLE:
 LOAD DISTINCT
 CUST_CODE_$(iLevel) as PARENT_CUST_CODE,
 CUST_CODE_$(iChildLevel) as CHILD_CUST_CODE
 RESIDENT STANDARD_HIER
 WHERE Not IsNull(CUST_CODE_$(iChildLevel));
NEXT iLevel;
LEFT JOIN (TEMP_HIER_TABLE)
LOAD CUST_CODE as CHILD_CUST_CODE,
 CUST_NAME as CHILD_CUST_NAME
RESIDENT CUSTOMER;
HIER_TABLE:
HIERARCHY (CHILD_CUST_CODE, PARENT_CUST_CODE, CHILD_CUST_NAME, PARENT_CUST_NAME, CHILD_CUST_NAME, HIER_PATH, '-')
LOAD CHILD_CUST_CODE,
 PARENT_CUST_CODE,
 CHILD_CUST_NAME,
 CHILD_CUST_CODE AS CUST_CODE
RESIDENT TEMP_HIER_TABLE;
DROP TABLE TEMP_HIER_TABLE;

The STANDARD_HIER table just stores the levels of the hierarchy.

Hope this helps!