Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a BOM File as follow,
Semi-Finished|Finished_Good_No | Semi-Finished|Finished_Good_Type | Raw-Material_No | Raw-Material_Type | Cumulative_Qty | unit_of_measure |
FG001200100 | Finished Good | RM001500100 | Raw Material | 0.001 | kg |
FG001200100 | Finished Good | RM001500101 | Raw Material | 0.45 | kg |
FG001200100 | Finished Good | RM001500102 | Raw Material | 0.05 | kg |
FG001200100 | Finished Good | RM001500103 | Raw Material | 0.65 | kg |
FG001200100 | Finished Good | RM001500104 | Raw Material | 1 | kg |
FG001200100 | Finished Good | SF008600100 | Semi-Finished Good | 1 | unit |
FG001200100 | Finished Good | SF008600101 | Semi-Finished Good | 1 | unit |
SF008600100 | Semi-Finished Good | RM001500100 | Raw Material | 0.7 | kg |
SF008600100 | Semi-Finished Good | RM001500101 | Raw Material | 0.65 | kg |
SF008600100 | Semi-Finished Good | RM001500109 | Raw Material | 0.4 | kg |
SF008600100 | Semi-Finished Good | RM001500110 | Raw Material | 1.25 | kg |
SF008600100 | Semi-Finished Good | SF008600121 | Semi-Finished Good | 1 | unit |
SF008600101 | Semi-Finished Good | RM001500100 | Raw Material | 0.25 | kg |
SF008600101 | Semi-Finished Good | RM001500101 | Raw Material | 0.003 | kg |
SF008600101 | Semi-Finished Good | RM001500102 | Raw Material | 0.55 | kg |
SF008600101 | Semi-Finished Good | RM001500107 | Raw Material | 0.8 | kg |
SF008600121 | Semi-Finished Good | RM001500106 | Raw Material | 0.001 | kg |
SF008600121 | Semi-Finished Good | RM001500117 | Raw Material | 0.076 | kg |
SF008600121 | Semi-Finished Good | RM001500118 | Raw Material | 0.009 | kg |
SF008600121 | Semi-Finished Good | RM001500119 | Raw Material | 0.089 | kg |
In the above table it specifies the Raw Material and Semi-Finished Goods Required to manufacture either Finished Goods or Semi-Finished Goods.
My requirement is to breakdown the above table to multiple level according the production, and drill down till the last level as follow;
Finished_Good | Level1_RM_Required | Cumulative_Qty | unit_of_measure | Level2_RM-Required | Cumulative_Qty | unit_of_measure | Level3_RM-Required | Cumulative_Qty | unit_of_measure |
FG001200100 | RM001500100 | 0.001 | kg | N/R | N/R | ||||
RM001500101 | 0.45 | kg | N/R | N/R | |||||
RM001500102 | 0.05 | kg | N/R | N/R | |||||
RM001500103 | 0.65 | kg | N/R | N/R | |||||
RM001500104 | 1 | kg | N/R | N/R | |||||
SF008600100 | 1 | unit | RM001500100 | 0.7 | kg | N/R | |||
RM001500101 | 0.65 | kg | N/R | ||||||
RM001500109 | 0.4 | kg | N/R | ||||||
RM001500110 | 1.25 | kg | N/R | ||||||
SF008600121 | 1 | unit | RM001500106 | 0.001 | kg | ||||
RM001500117 | 0.076 | kg | |||||||
RM001500118 | 0.009 | kg | |||||||
RM001500119 | 0.089 | kg | |||||||
SF008600101 | 1 | unit | RM001500100 | 0.25 | kg | N/R | |||
RM001500101 | 0.003 | kg | N/R | ||||||
RM001500102 | 0.55 | kg | N/R | ||||||
RM001500107 | 0.8 | kg | N/R |
In the above table, I have drill down till only 3 levels, however this could be changed according to the product.
Please advise how this could be achieved on qlik sense. Please find the attached image before for a better understanding.
This is potentially a quite advanced problem. Would it work for you to join and rename the fields a number of times? Like
Load
[Semi-Finished|Finished_Good_No] as Finished_Good,
Raw-Material_No as MaterialNo1,
Cumulative_Qty as Cumulative_Qty1
from BOM.qvd (qvd) where [Semi-Finished|Finished_Good_Type]='Finished Good';
left join
Load
[Semi-Finished|Finished_Good_No] as MaterialNo1,
Raw-Material_No as MaterialNo2,
Cumulative_Qty as Cumulative_Qty2
from BOM.qvd (qvd);
left join
Load
[Semi-Finished|Finished_Good_No] as MaterialNo2
Raw-Material_No as MaterialNo3,
Cumulative_Qty as Cumulative_Qty3
from BOM.qvd (qvd);
And so on...
Morgan Kejerhag
Senior Business Intelligence Consultant
Drake Analytics
http://www.drakeanalytics.se
Try like this,
tab1:
Hierarchy([Raw-Material_No],[Semi-Finished|Finished_Good_No],Name,,[Semi-Finished|Finished_Good_No],'Path','/',Depth)
LOAD *, [Raw-Material_No] As Name;
LOAD * INLINE [
Semi-Finished|Finished_Good_No, Semi-Finished|Finished_Good_Type, Raw-Material_No, Raw-Material_Type, Cumulative_Qty, unit_of_measure
FG001200100, Finished Good, RM001500100, Raw Material, 0.001, kg
FG001200100, Finished Good, RM001500101, Raw Material, 0.45, kg
FG001200100, Finished Good, RM001500102, Raw Material, 0.05, kg
FG001200100, Finished Good, RM001500103, Raw Material, 0.65, kg
FG001200100, Finished Good, RM001500104, Raw Material, 1, kg
FG001200100, Finished Good, SF008600100, Semi-Finished Good, 1, unit
FG001200100, Finished Good, SF008600101, Semi-Finished Good, 1, unit
SF008600100, Semi-Finished Good, RM001500100, Raw Material, 0.7, kg
SF008600100, Semi-Finished Good, RM001500101, Raw Material, 0.65, kg
SF008600100, Semi-Finished Good, RM001500109, Raw Material, 0.4, kg
SF008600100, Semi-Finished Good, RM001500110, Raw Material, 1.25, kg
SF008600100, Semi-Finished Good, SF008600121, Semi-Finished Good, 1, unit
SF008600101, Semi-Finished Good, RM001500100, Raw Material, 0.25, kg
SF008600101, Semi-Finished Good, RM001500101, Raw Material, 0.003, kg
SF008600101, Semi-Finished Good, RM001500102, Raw Material, 0.55, kg
SF008600101, Semi-Finished Good, RM001500107, Raw Material, 0.8, kg
SF008600121, Semi-Finished Good, RM001500106, Raw Material, 0.001, kg
SF008600121, Semi-Finished Good, RM001500117, Raw Material, 0.076, kg
SF008600121, Semi-Finished Good, RM001500118, Raw Material, 0.009, kg
SF008600121, Semi-Finished Good, RM001500119, Raw Material, 0.089, kg
];
This is potentially a quite advanced problem. Would it work for you to join and rename the fields a number of times? Like
Load
[Semi-Finished|Finished_Good_No] as Finished_Good,
Raw-Material_No as MaterialNo1,
Cumulative_Qty as Cumulative_Qty1
from BOM.qvd (qvd) where [Semi-Finished|Finished_Good_Type]='Finished Good';
left join
Load
[Semi-Finished|Finished_Good_No] as MaterialNo1,
Raw-Material_No as MaterialNo2,
Cumulative_Qty as Cumulative_Qty2
from BOM.qvd (qvd);
left join
Load
[Semi-Finished|Finished_Good_No] as MaterialNo2
Raw-Material_No as MaterialNo3,
Cumulative_Qty as Cumulative_Qty3
from BOM.qvd (qvd);
And so on...
Morgan Kejerhag
Senior Business Intelligence Consultant
Drake Analytics
http://www.drakeanalytics.se
Try like this,
tab1:
Hierarchy([Raw-Material_No],[Semi-Finished|Finished_Good_No],Name,,[Semi-Finished|Finished_Good_No],'Path','/',Depth)
LOAD *, [Raw-Material_No] As Name;
LOAD * INLINE [
Semi-Finished|Finished_Good_No, Semi-Finished|Finished_Good_Type, Raw-Material_No, Raw-Material_Type, Cumulative_Qty, unit_of_measure
FG001200100, Finished Good, RM001500100, Raw Material, 0.001, kg
FG001200100, Finished Good, RM001500101, Raw Material, 0.45, kg
FG001200100, Finished Good, RM001500102, Raw Material, 0.05, kg
FG001200100, Finished Good, RM001500103, Raw Material, 0.65, kg
FG001200100, Finished Good, RM001500104, Raw Material, 1, kg
FG001200100, Finished Good, SF008600100, Semi-Finished Good, 1, unit
FG001200100, Finished Good, SF008600101, Semi-Finished Good, 1, unit
SF008600100, Semi-Finished Good, RM001500100, Raw Material, 0.7, kg
SF008600100, Semi-Finished Good, RM001500101, Raw Material, 0.65, kg
SF008600100, Semi-Finished Good, RM001500109, Raw Material, 0.4, kg
SF008600100, Semi-Finished Good, RM001500110, Raw Material, 1.25, kg
SF008600100, Semi-Finished Good, SF008600121, Semi-Finished Good, 1, unit
SF008600101, Semi-Finished Good, RM001500100, Raw Material, 0.25, kg
SF008600101, Semi-Finished Good, RM001500101, Raw Material, 0.003, kg
SF008600101, Semi-Finished Good, RM001500102, Raw Material, 0.55, kg
SF008600101, Semi-Finished Good, RM001500107, Raw Material, 0.8, kg
SF008600121, Semi-Finished Good, RM001500106, Raw Material, 0.001, kg
SF008600121, Semi-Finished Good, RM001500117, Raw Material, 0.076, kg
SF008600121, Semi-Finished Good, RM001500118, Raw Material, 0.009, kg
SF008600121, Semi-Finished Good, RM001500119, Raw Material, 0.089, kg
];
Output:
Hi Morgan,
Thanks for the solution provided, and it works for the requirement I have given above. As you have mentioned, the number of levels could vary according to the product for example;
Product A can have 3-level breakdown, while the Product B has 5-level breakdown. Is there any possibilities to make this script dynamic than making it static?
I am working on it, and appreciate you inputs as well.
Thanks,
Kishore
Hi Saran,
Thanks for the solution given above, this works fine perfectly for both static and dynamic datasets.
Thanks,
Kishore