Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Left Keep Hierarchy or HeirarchyBelongsTo

Please help...

I have a few instances where I have some enterprise-wide heirarchies that I'd like to use when data is available for nodes that exist on them.

The challenge is: how to show only the subset of the hierarchy nodes and all of their ancestors.

My attempt (that doesn't work) was to use a Left Keep onto an ancestor table.  For some reason the filter effect applier to the Ancestor IDs as well as the node IDs resulting in a much smaller record set than I was looking for.

Is this a bug?  Is there a workaround?

Thanks.

4 Replies

Re: Left Keep Hierarchy or HeirarchyBelongsTo

It is not a bug - it is just that the Hierarchy prefixes are slightly special: You want to have all records for the hierarchy transformation to work. So you cannot use a Where-clause  or a left keep on it.

But you can reduce the records after the transformation is made, hence, the following works:

SalesData:

LOAD ID, Month, Sales FROM SampleData.xlsx (ooxml, embedded labels, table is SalesData);

SalesPerson:

HierarchyBelongsTo(ID, ManagerID, Name, AncestorID, ManagerName, DepthDiff)

LOAD ManagerID, Manager, Name, ID FROM SampleData.xlsx (ooxml, embedded labels, table is SalesHierachy);

Right Join Load distinct ID Resident SalesData;

HIC

Not applicable

Re: Left Keep Hierarchy or HeirarchyBelongsTo

Henric

can you help me understand some business cases where using a Hierarchy in Qlikview is the preferable method?  I have not used this method and wondering if I have some instances where it might be useful.

Re: Left Keep Hierarchy or HeirarchyBelongsTo

The most obvious case is if you have a hierarchy in the Adjacent Nodes form in your database. (I.e. a table that contains the fields ID and ParentID, and ParentID points at the ID of another record of the same table.) This could be the case for Product groups/Products, Sales regions, Projects/Subprojects, Cost accounts, The internal organzation, etc. Then you absolutely should use the QlikView hierarchy prefix.

If you don't have an adjacent nodes table in your DB, then you should probably avoid the hierarchy prefix...

See more on http://community.qlik.com/docs/DOC-5334

HIC

Not applicable

Re: Left Keep Hierarchy or HeirarchyBelongsTo

Thank you Henric