Hierarchies and Product Sums

[matthewjbryant]

Hi All,

I have been working on a part hierarchy in SQL for a week and thought I'd see if Qlikview could help me, but after a lot of work I'm beginning to think that Qlikview can't achieve what I need. I have read Hierarchy in QlikView by Fabrice Aunez and Hierarchies by Henric Cronström, but I'm having real issues modelling my data. I have an unbalanced, n-level, ragged Adjacency List of parts and component parts (a Bill Of Materials). What's more, I am only interested in certain root parts that may or may not be the actual root of the hierarchy. How can I pick these out an show a table of the nodes leading from these parts?

Following that, I want to be able to click on a leaf part (the furthest away from the root in the hierarchy) and see a Product Sum (i.e. row1 x row2 x ... x rown) of the Quantity column from all parts (including the root) that lead to this one. Is this at all possible?

I've attached a mock-up of the structure I'm dealing with. The page on the the spreadsheet entitled 'Required Table' shows what I want to see in a table, but I can't get. Is anyone able to edit my QVW to obtain this result?

[tresesco]

Go through this blog post: Hierarchies that tells you how you have to proceed. Certainly that it is not the limit of QlikView's functionality.

[matthewjbryant]

Thanks. I'll check that out now. I've just edited my question to explain a little better.

[aveeeeeee7en]

Yes, It is Possible.

Add Levels in Hierarchy Code.

Levels will create as per the Reporting Order.

Following is the Script:

Hierarchy (EmpID,[Reporting To],Name,Manager,Name,EmpTree,'/',Levels)
LOAD Distinct *
FROM
......\Hierarchy.xlsx]
(ooxml, embedded labels, table is Sheet1);

Please find the Attachment.

Hope that works for you.

Regards

Aviral Nag

[tresesco]

Use HierarchyBelongsTo and then a bit of tricky approach like using parent and tree fields in the fornt-end would help achieve what you want.

[matthewjbryant]

Thanks. That Level function is a helpful addition, but I don't know if it's going to help me here yet because I have a ragged, unbalanced hierarchy. It may well help in another way though!

[matthewjbryant]

Thanks for the hints! So I have a hierarchy based on an Adjacency List and using the HierarchyBelongsTo I can make an Ancestor Table. Is that right?

Also, a quick question: is there a way to limit the records loaded to those that link back to a single root? In SQL you can use the START WITH command in addition to CONNECT BY.

I've edited my question extensively to add files now. I've tried a lot of the techniques talk about in those blog posts, but still cannot get this to work! Any more hints?