Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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?
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!
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?