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.
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:
LOAD ID, Month, Sales FROM SampleData.xlsx (ooxml, embedded labels, table is SalesData);
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.
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...