Counting the correct quantities in a multi-level bill of materials
Hello,
I have a data from SQL database, which is used by our ERP. Structure of table is multi-level BOM. What does it mean ?
Here is some example of data (Important information is - Quantities are stored per one unit; I need to scale them for multiple units.
Manufactured Item
Position
Item
Quantity
A10250001V353551
10
M700001
4
A10250001V353551
20
M700006
6
A10250002V353552
10
A10250002V353553
2
A10250002V353552
20
M700009
10
A10250002V353553
10
A10250002V353554
1
A10250002V353553
20
M700005
2
A10250002V353554
10
M700002
1
A10250003V353555
10
A10250003V353556
2
A10250003V353555
20
M700004
1
A10250003V353556
10
M700006
2
.... etc.
So, the item A10250001V353551 is always parent, so the item is single-level.
But, the item A10250002V353552 is multi-level, because first lower part of BOM is A10250002V353553 and also the item A10250002V353553 has other lower part of BOM - A10250002V353554. And If I want to manufactured 3 pieces of A10250002V353552, than I need 6 pieces of A10250002V353553, and 30 pieces of M700009 ... etc.
The data needs to be scripted correctly in order for Qlik Sense to properly summarize the quantities.
Could you advise me on how to script this so that it calculates correctly? Thank you very much.
