QlikView hierarchy functions expect a table with the columns NodeID, NodeName and ParentNodeID. The table you have in Excel actually would be result the Hierarchy() function would give you. Now if you want have a result like the HierarchyBelongsTo() would give you with all the nodes in one column try first creating a table with NodeID, NodeName and ParentNodeID with the following script:
Node_Parent_Table: Load distinct Product as NodeID, Product_Description as NodeName, [(EPRD6) Essbase Level 2] as ParentNodeID From x.xls; concatenate Load distinct [(EPRD6) Essbase Level 2] as NodeID, [Level 2 Desc] as NodeName, [(EPRD5) Essbase Level 3] as ParentNodeID From x.xls; concatenate etc...
then call the Node_Parent_Table with the resident command and do a hierarchybelongsto() function over that table.
Okay I dont have enough memory to do this function for all the levels i get an allocating more memory error followed by a crash, but if i do just the top three levels it works. Once i have the set up like
You should be able to do the treeview selector without doing what I mention. Just load an additional field to the flat file you have that is like the following:
Good point. I think you're right, so you'd have to do something like the following, but I don't think you'll be able to do it with your limited memory.
Load Level1, sum(Total) as Total, Level1 as TreeViewField From ... Group by Level1;
concatenate
Load Level1,Level2, sum(Total) as Total, Level1 & '/' & Level2 as TreeViewField From ... Group by Level1,Level2;
Good point. I think you're right, so you'd have to do something like the following, but I don't think you'll be able to do it with your limited memory.
Load Level1, sum(Total) as Total, Level1 as TreeViewField From ... Group by Level1;
concatenate
Load Level1,Level2, sum(Total) as Total, Level1 & '/' & Level2 as TreeViewField From ... Group by Level1,Level2;
