topic BOM Explosion using Hierarchy & HierarchyBelongsTo in QlikView

BOM Explosion using Hierarchy & HierarchyBelongsTo

Thu, 22 Jul 2010 14:13:17 GMT

I'm attempting to perform a bill of materials explosion in QV using Hierarchy & HierarchyBelongsTo. My data looks something like this:

Parent	Child	Type	Qty
A	B	Make	2
A	E	Buy	1
A	F	Buy	1
B	C	Make	2
C	D	Buy	2
D	G	Make	1
G	H	Buy	1

My goal it to get a flat table depicting the total BOM structure for several hundred thousand parts (perhaps millions of parts). The resulting table would look something like this:

Parent	Child	Type	Qty	Level
A	A	Make	1	0
A	B	Make	2	1
A	C	Make	4	2
A	D	Buy	8	3
A	E	Buy	1	1
A	F	Buy	1	1
B	B	Make	1	0
B	C	Make	2	1
B	D	Buy	4	2
D	D	Make	1	0
D	G	Make	1	1
D	H	Buy	1	2
G	G	Make	1	0
G	H	Buy	1	1

Hierarchy seems to get me close, but, I'm missing top level parent ID and am not sure how to calculate the expanded quantities. Any ideas?

BOM Explosion using Hierarchy & HierarchyBelongsTo

Mon, 02 Aug 2010 17:35:05 GMT

Any one have any ideas???

BOM Explosion using Hierarchy & HierarchyBelongsTo

suniljain — Wed, 06 Oct 2010 14:53:15 GMT

Pls Try Attached File

BOM Explosion using Hierarchy & HierarchyBelongsTo

Wed, 02 Mar 2011 10:00:37 GMT

anyone solved this problem....

could anyone tell me how to solve this issue....

BOM Explosion using Hierarchy & HierarchyBelongsTo

Fri, 03 Feb 2012 14:38:25 GMT

Same problem, still no solution 🙂

Did anyone solve this yet? I'm trying to combine a Hierarchy with another HierarchyBelongsTo on the same source. The solution seems so close and yet so far away...

Re: BOM Explosion using Hierarchy & HierarchyBelongsTo

hic — Fri, 03 Feb 2012 15:20:34 GMT

Two points:

1) You need to have this in two tables, since you can have several transactions on the same node.

2) You need to define a root node – i.e. a node with NULL as parent. I have added this to the source table, but this can be done in the script instead.

Re: BOM Explosion using Hierarchy & HierarchyBelongsTo

Fri, 03 Feb 2012 15:32:16 GMT

Thx for replying.

To meet my reporting requirements i need a BOM bridge table like this:

child, ancestor, Qty

A, A, 1

A, B, 2

A, C, 4

A, D, 8

B, B, 1

B, C, 2

B, D, 4

C, C, 1

C, D, 2

D, D, 1

Then in the report, child will relate to an article table and ancestor will relate to an invoice line table. So when i select A, i want to calculate the total sales qty amount even for invoice lines that where booked on B, C or D.

I don't think that the example you made solves this problem.

Re: BOM Explosion using Hierarchy & HierarchyBelongsTo

hic — Fri, 03 Feb 2012 15:45:20 GMT

I am fairly sure that the pivot table calculates exactly what you want. On the group for ‘A’, Qty’s for all its sub-nodes will be summed.

If you instead use HierarchyBelongsTo, you will get a bridge table which will give you an Ancestor list box, which could be useful, but it is not necessary for the summation. See new attachment.

Further, if you put both the Ancestor and the Qty in the same table, you will double-count the sold units: the same unit will appear on several lines.

Re: BOM Explosion using Hierarchy & HierarchyBelongsTo

hic — Fri, 03 Feb 2012 15:51:56 GMT

... but if you really want the bridge table with quantities in it, then just take the HierarchyBelongsTo table in my example and join it with the original transaction table. (Don't load Parent in the transaction table).

Re: BOM Explosion using Hierarchy & HierarchyBelongsTo

Fri, 03 Feb 2012 16:00:08 GMT

Also did this myself, but how do i calculate how many times G is in A (from your example it should be 8)...

In your example, if i make a table graph with NodeID and AncestorID in it as dimensions and sum(Qty) as expression, the numbers don't give what i need.

Re: BOM Explosion using Hierarchy & HierarchyBelongsTo

hic — Fri, 03 Feb 2012 16:14:45 GMT

I do not understand where you think it is wrong. The pivot table in my example shows the same numbers as in the original data - which I assume is what it should. E.g. 'A' has 10 units and 'B' has 8. Do you have an example of where the result is wrong?

Re: BOM Explosion using Hierarchy & HierarchyBelongsTo

Fri, 03 Feb 2012 16:26:51 GMT

OK i will try to test your example by adding some fake invoice lines to the data model. But only after the weekend, so thanks for helping me so far and have a good weekend yourself!

Re: BOM Explosion using Hierarchy & HierarchyBelongsTo

Wed, 03 May 2017 17:13:49 GMT

I hope you are still active Henric...

Do I need to insert just a single root node or one for every distinct parent? How would the script look to do either?