Hierachy: create parent/child relation

[#1]

Hi Qliks!

I am stuck with the following issue concerning hierachies. I have the following table representing the setup of my Bill of Material (three structure) table:

The "Level" field indicates the "parent/child" relationship within each BOM ID.

How can I use the "Level" field to build a hierarchy structure that would allow drill down in a pivot table and maybe even a three structure in a list box. Pls. note that the order of the records is important - e.g. level 2 sums up into two different level 1 items.

As always I appreciate your feedback

Regards,

Lars

[#2]

Hello,

Does that can help ?

Philippe

[#3]

Thank you for your reply Philippe

Will you be able to build the (level) hierachy into a pivot table - meaning that I will only have one expression in the table. The three will be formed in the dimension = Item_ID allowing to drill down based on the Level identifyer.

Sorry .. hierarchy load statement is not too familiar to me and frankly speaking I do not get much help from the hirarchy wizard included in QV 9 [:'(]

Regards,

Lars

[#4]

something like that?

[#5]

Philippe

I realize that I might have caused some confusion about my desired output by putting in the second illustration in the first post. I recon that we somehow have to convert the first table:

Into this:

• Notice that only the lowest levels have been included meaning that the sum of Value = 100
• The relation between the items is achieved via the columns L0, L1, L2 and L3

This should make it possible to generate a "drill-down" pivot table in QlikView like this:

How to make the conversion:

• First the lowest level items must be identified. I recon that you could load the table in reverse order and then use Peek() on the Level field to identify if an Item has a lower level item included in the table
• How to generate the parent/child relationship on the ItemID field
• How to put that information into seperate columns like in second illustration above. Note for example, that I want e.g. the B item identifier to show on all levels since that would make the best representation in the pivot table rather than leaving it blank.

I appologize for the confusion - pls. be patient with me Philippe

Lars

[johnw]

I see two ways of extracting the hiearchy from your data, and I don't know which is real and which is just an artifact of how you presented the data:

1. Sequence of records is meaningless, level 0 defines the root for a BOM_ID, rest of hierarchy defined by meaningful Item_IDs
2. Item_ID is meaningless, extract hiearchy from the level and the sequence of the records
3. Something else?

It seems like you want to extract the hiearchy information from the levels and sequence, but if so, why do the IDs represent the position in the hierarchy as well? Or if you want the IDs to represent the position in the hieararchy, why have the level and arrange your data in a specific sequence?

I have a similar question regarding the Amount. Having summary amounts at all levels just gets in the way of a legitimate hiearchy. Are these simply a feature of your source data, so must be removed by the script? Or are they just an artifact of how you're trying to show us that this is a hiearchy?

Here's an example that produces exactly the results you want. It assumes that the sequence of records is meaningless, level 0 defines the root for a BOM_ID, and the rest of the hiearchy is defined by meaningful Item_IDs. It also assumes that the subtotal amounts are not required to be in your source data. Since these assumptions may be completely wrong, my solution might also be completely wrong. But it produces the output.

[#7]

Hi John

I guess that sometimes when trying to make a general example that is supposed to nail down the issue you accidentially cause some confusion. Anyway - you pretty much make the right assumptions and has become very close to what I am aiming at.

Your suggestion #2 is the right one since Level should be used together with row() (since the position in the original data set is important).

The reason why ItemID represents the hierachy as well was just for my convenience - this is not the case in the live data so we can not count on that. Regarding Amount - yes you are also right. The summary Amount shouldn't be loaded from the live data which in fact includes all the summary amounts as seperate records - I have tried to solve that by this code:

LOAD *, IF(BOM_ID <> PEEK(BOM_ID),Amount, if(Level>=Peek(Level),Amount)) as Lowest_level_value

RESIDENT table

ORDER BY LineNum DESC

Anyway - what is left for me now is to solve the following two problems:

• How do I generate the parent information without using the build in logic in the ID string from my small example. I have been trying to use the Level field and position to peek into the previous record(s) for the information but without much luck (since I sometimes need to look back more than one line)
• Secondly: I realized that if I extent my data with mutible BOM_IDs my number of records multiplied with the number of distinct BOM_IDs. I solved this by reloading the table at the end with "Load Distinct" but this is a very dirty trick I guess...

I short - how to do the trick if the data looks like this:

LOAD * INLINE [

BOM_ID, Item_ID, Level, Amount, LineNum

209, 055710-345027, 0, 50, 1

210, 055700-345027, 0, 100, 2

210, 26380, 1, 25, 3

210, 2323, 1, 50, 4

210, 492017, 2, 20, 5

210, 70503, 2, 30, 6

210, 43303, 3, 15, 7

210, 547001, 3, 15, 8

210, 547101, 1, 10, 9

210, 25835, 2, 5, 10

210, 79116, 2, 5, 11

210, 138316, 1, 15, 12

211, 055730-345027, 0, 25, 13

];

Where:

• More than one BOM_ID exists
• No logic in the ITEM_ID
• LineNum has been added and will together with Level indicate the Parent/Child relationship

As always - your responses are really valuable John

Lars

[johnw · Accepted solution]

OK, I think I have it, then. One improvement would be to convert it to a real hierarchy, as I think there are a couple things that QlikView does with a real hierarchy that it won't do with my "fake" hierarchy. Shouldn't be too much trouble if that's necessary.

This has been a good exercise for me, as I normally deal with very little hierarchical data, so any practice I can get is good.

[#9]

Hi John

This is perfect - a simple solution which is exactly how we like it!! I just applied it to my live scenario with lots of items and hierarchies and it works perfectly.

I might want to give it a try with the hierarchy load since I would like to setup a three view in a list box.

Thank you very much John,

Lars