topic Re: Hierarchy Load Question in QlikView

Hierarchy Load Question

Anonymous — Fri, 08 Nov 2013 22:54:24 GMT

I am limiting the records to a hierarchy load using "where exists()" to the Fact table. After the load all the Parents are not selectable in the tree view list box. Is there a way to make the selectable in the document?

Re: Hierarchy Load Question

Fri, 08 Nov 2013 23:16:01 GMT

This one for me was so usefull with somthin like your issue, sorry for my english

BestRegards!!

Re: Hierarchy Load Question

mbm — Sat, 09 Nov 2013 00:40:31 GMT

Michael,

I'm leaving out the complexities of the hierarchy statement and focusing specifically on why the TreeView is not allowing selections in your example. In order for the nodes in a treeview to be selectable they must exist in their own right in the data. In the example below you can see that Node1 contains the proper value to be parse into a tree view list box. In this situation the listbox will be unselectable for all but the last node value 6462

UnselectableParentNodes:

LOAD * INLINE [
    Node1
    PCBI/PCBI1/PCLN3/PCLN31/6462
];

The script bellows shows what the data would need to look like in order to make all parent nodes selectable.

SelectableParentNodes:

LOAD * INLINE [
    Node2
    PCBI

PCBI/PCBI1

PCBI/PCBI1/PCLN3

PCBI/PCBI1/PCLN3/PCLN31

PCBI/PCBI1/PCLN3/PCLN31/6462
];

Given both scripts - you can see what the data would appear like in the QVW. I've attached the QVW as well if you care to see it working.

Re: Hierarchy Load Question

andrewpettit — Sat, 09 Nov 2013 02:43:53 GMT

Simply put if you have a the following data the parent node for A2 will be selectable and B2 will not. This is because there is a tree path value that when parsed terminates at A2, however for B2 no such record exists.

A1 / A2

A1 / A2 / A3

A1 / A2 / B3

B1 / B2 / B3

B1 / B2 / C3

Hope this helps explain why some of the values in your hierarchy are not selectable.

Re: Re: Hierarchy Load Question

Anonymous — Sat, 09 Nov 2013 05:22:39 GMT

Thanks for the response. I found the attached document that may solve my issue though I have only briefly reviewed it.

Re: Hierarchy Load Question

Gysbert_Wassenaar — Sat, 09 Nov 2013 10:53:12 GMT

Perhaps you want to use the HierarchyBelongsTo function instead of the Hierarchy function.

Re: Hierarchy Load Question

Sat, 09 Nov 2013 14:16:57 GMT

Hi,

it is due to the fact that the parents do not have any data.

Please, read a doc I have written on the subject that explain the 2 or 3 tricks to do to have a complete hierarchy:

http://community.qlik.com/docs/DOC-4823

Fabrice

Re: Hierarchy Load Question

Anonymous — Sat, 09 Nov 2013 23:24:00 GMT

Thanks, this actually solved the other issue I had where I wanted the selection of the Parent to include the Children even if the Parent is not in the Fact Table.

However, my current issue I don't see a resolution, at least with my limited abilities, to limit the Hierarchy load to only the children that exist in the fact table and not exclude the Parent out of the Hierarchy Load without rebuilding a heirarchy table that is dedicated to just this QV Application. If I load all the children the Treeview list box works properly but I have a lot of children that do not contain values and are unnecessary to the document.

Re: Hierarchy Load Question

Anonymous — Sun, 10 Nov 2013 23:53:23 GMT

Below is the Solution. I created a Link Table using Hierarchy Belongs To described in FABRICE44 article and then used Where Exists on the Link Table to limit it to the Fact Table. Finally I do a Resident Load on the original Hierarchy Load and left keep it to the Link Table.

Directory;

[Center Temp]:

HIERARCHY([%CenterID],[Parent],[Center - Name],,[Center - Name],[Center Rollup], '*',CenterDepth)

LOAD

Child as %CenterID,

Parent,

[Center Name] as [Center Name],

Child & ' - ' & [Center Name] as [Center - Name]

FROM

[\\whpf3001\QlikView$\1. Source Data\Dimension Tables\TM1 Dimensions.xlsx]

(ooxml, embedded labels, table is Center);

Directory;

[Center Temp2]:

HierarchyBelongsTo([%CenterID],[Parent],[Center - Name],'Ancestor Key','Ancestor Name')

LOAD

Child as %CenterID,

Parent,

[Child ID] as [%Center Child ID],

[Parent ID] as [%Center Parent ID],

[Center Name] as [Center Name],

Child & ' - ' & [Center Name] as [Center - Name]

FROM

[\\whpf3001\QlikView$\1. Source Data\Dimension Tables\TM1 Dimensions.xlsx]

(ooxml, embedded labels, table is Center)

;

Link:

LOAD [Ancestor Key] as %CenterID,

%CenterID as Center

Resident [Center Temp2]

Where EXists(Center,%CenterID);

CentersDimension:

LEFT KEEP(Link)

Load

%CenterID,

[Center Rollup]

Resident [Center Temp]

;

DROP TABLE [Center Temp];

DROP TABLE [Center Temp2];