Hierarchy Load Question

[Anonymous]

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?  

[#2]

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

BestRegards!!

[mbm]

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.

[andrewpettit]

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.

[Anonymous]

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

[Gysbert_Wassenaar]

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

[#7]

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

[Anonymous]

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.

[Anonymous · Accepted solution]

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