topic Re: Left Keep Hierarchy or HeirarchyBelongsTo in QlikView

Left Keep Hierarchy or HeirarchyBelongsTo

Tue, 17 Dec 2013 11:45:22 GMT

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.

Re: Left Keep Hierarchy or HeirarchyBelongsTo

hic — Fri, 17 Jan 2014 14:22:55 GMT

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

Re: Left Keep Hierarchy or HeirarchyBelongsTo

Fri, 17 Jan 2014 15:04:30 GMT

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

hic — Fri, 17 Jan 2014 15:19:02 GMT

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

Re: Left Keep Hierarchy or HeirarchyBelongsTo

Fri, 17 Jan 2014 15:23:08 GMT

Thank you Henric