Counting pieces in the bill of materials

[jozisvk11]

Hello,

I have SQL database from our ERP system with the following struckture. It is normal bill of material - the lower part enters the higher part etc. 

The number of BOM levels can be unlimited !!!

Items with blue colour are the highest part of BOM, which are the final product for customer. Order number is the first 9 characters from 'Manufactured Item'. 

Manufactured Item	Position	Item	Quantity
T10230001V123456	1	M124578	10
T10230001V123456	2	M124711	5
T10230001V123457	1	T10230001V123456	2
T10230002V457831	1	M424814	1
T10230002V457832	1	T10230002V457831	1
T10230003V124578	1	M547941	1
T10230003V124579	1	T10230003V124578	1
T10230003V124579	2	M457142	1
T10230003V124580	1	T10230003V124579	1
T10230004V124599	1	M457891	2
T10230004V124600	1	M547897	2
T10230004V124601	1	T10230004V124599	2
T10230004V124601	2	T10230004V124600	2
....

I  need to count the right amount of all material in BOM correctly. 

The final table should be:

ORDER NUMBER	ITEM	QUANTITY
T10230001	M124578	20	(Note: I need 20, because 10 x 2 pieces of final product)
T10230001	M124711	10	(Note: I need 10, because 5 x 2 pieces of final product)
T10230002	M424814	1
T10230003	M547941	1
T10230003	M457142	1
T10230004	M457891	4	(Note: I need 4, because 2 x 2 pieces of final product)
T10230004	M547897	4	(Note: I need 4, because 2 x 2 pieces of final product)

Could you help me, how can i write a script ???

Thank You.

[Kushal_Chawda · Accepted solution]

@jozisvk11  try below. I have used where condition according to your sample data. You need to modify your where condition to identify actual and final products from "Item" field.

Data:
LOAD
    left("Manufactured Item",9) as [Order Number],
    "Manufactured Item",
    Position,
    Item,
    Quantity
FROM Table
where Item like 'M*';

Left Join(Data)
Load distinct Item as "Manufactured Item",
              Quantity as final_product_qty
FROM Table
where Item like 'T*';

Final:
NoConcatenate
Load [Order Number],
     Item,
     Quantity*final_product_qty as Final_Qty
Resident Data;

Drop Table Data;

[BrunPierre · Accepted solution]

Hi, there are undoubtedly more elegant ways, but here is one.

Tmp1:
LOAD [Manufactured Item], 
     Position, 
     Item, 
     Quantity
   
FROM
[https://community.qlik.com/t5/App-Development/Counting-pieces-in-the-bill-of-materials/td-p/2070388]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

NoConcatenate
Tmp2:
LOAD Left([Manufactured Item],9) as [Order Number],
                    Quantity as [Pieces of Final Product]
 
Resident Tmp1          
Where Len(Item) = 16;

Left Join(Tmp2)
LOAD   Left([Manufactured Item],9) as [Order Number],
              Item,
             Quantity
              
Resident Tmp1           
Where Len(Item) = 7;

NoConcatenate
"Final Table":
LOAD [Order Number],
            Item,
            Quantity * [Pieces of Final Product]  as Quantity
             
Resident Tmp2;

DROP Tables Tmp1, Tmp2;

EXIT SCRIPT;

[jozisvk11]

Hello  Kushal_Chawda,

It works good.

But I have small problem. The problem is, if the material is on the highest part of the BOM.

For example:

Manufactured Item	Position	Item	Quantity
T10230001V123456	1	M124578	10
T10230001V123456	2	M124711	5
T10230001V123457	1	T10230001V123456	2
T10230002V457831	1	M424814	1
T10230002V457832	1	T10230002V457831	1
T10230003V124578	1	M547941	1
T10230003V124579	1	T10230003V124578	1
T10230003V124579	2	M457142	1
T10230003V124580	1	T10230003V124579	1
T10230004V124599	1	M457891	2
T10230004V124600	1	M547897	2
T10230004V124601	1	T10230004V124599	2
T10230004V124601	2	T10230004V124600	2
T10230004V124601	3	M547899	5

So the final table should be:

ORDER NUMBER	ITEM	QUANTITY
T10230001	M124578	20	(Note: I need 20, because 10 x 2 pieces of final product)
T10230001	M124711	10	(Note: I need 10, because 5 x 2 pieces of final product)
T10230002	M424814	1
T10230003	M547941	1
T10230003	M457142	1
T10230004	M457891	4	(Note: I need 4, because 2 x 2 pieces of final product)
T10230004	M547897	4	(Note: I need 4, because 2 x 2 pieces of final product)
T10230004	M547899	5	This item I need to add to my final table.

Could you help, how can I solve this problem ?

Thank you.

[Kushal_Chawda · Accepted solution]

@jozisvk11  modify your code bit like below

Data:
LOAD
    left("Manufactured Item",9) as [Order Number],
    "Manufactured Item",
    Position,
    Item,
    Quantity
FROM Table
where Item like 'M*';

Left Join(Data)
Load distinct Item as "Manufactured Item",
              Quantity as final_product_qty
FROM Table
where Item like 'T*';

Final:
NoConcatenate
Load [Order Number],
     Item,
     if(IsNull(final_product_qty),Quantity, Quantity*final_product_qty) as Final_Qty
Resident Data;

Drop Table Data;

[jozisvk11]

Thank you a lot. It works good.