Discussion Board for collaboration on QlikView Scripting.
It was requested of me to build a report that calculates the current cost of a specific product.
Let's call this product. Product A. Product A is the Father Product in this case.
Product A consists of two components, Product B & C. The quantity required of each component
to produce Product A is shown in the Qty field. The cost per unit of each component is shown in Unit Cost field.
|F00014829||Product A||HZA000991||Product B||29.44|
|F00014829||Product A||PZA00095||Product C||1.00||56.27|
The issue I am having is that Product B is a Father Product on its own as well. There is a number of components required to produce Product B.
|HZA000991||Product B||R00025457||Component B1||0.00||691.12|
|HZA000991||Product B||R00027720||Component B2||0.19||32.59|
|HZA000991||Product B||R00032427||Component B3||0.42||9.23|
|HZA000991||Product B||R00033164||Component B4||0.08||33.84|
|HZA000991||Product B||R00034256||Component B5||0.15||4.99|
|HZA000991||Product B||R00036131||Component B6||0.05||42.08|
|HZA000991||Product B||R00039869||Component B7||0.00||233.78|
|HZA000991||Product B||R00056138||Component B8||0.10||39.00|
|HZA000991||Product B||R00084788||Component B9||0.00||76.69|
|HZA000991||Product B||R00098034||Component B10||0.00||67.46|
|HZA000991||Product B||R01038421||Component B11||0.00||170.63|
What I want at the end is to select Product A. Qlikview should then calculate how much it will cost to produce Product A. Qlikview should automatically calculate how much it will cost to produce one unit of product B. This cost of product B should then be added to the Product B (as a component of Product A).
The total cost of one unit of product A should be 660.65. This is the correct answer.
Please find attached excel sheet. This excel sheet is an extract of the database we are using. I think the hierarchy function should be use in some way, but I am not sure how.
There you go:
(biff, embedded labels, table is Sheet1$) ;
LOAD FatherCode as ChildCode,
sum(Qty*[Unit Cost]) as [Unit Cost Child]
(biff, embedded labels, table is Sheet1$) group by FatherCode;
load FatherCode,FatherName,ChildCode,ChildName,Qty,if(isnull([Unit Cost]),[Unit Cost Child],[Unit Cost]) as [Unit Cost] resident Temp;
drop table Temp;
Just to answer your questions:
Is there a way to tell which are finished goods? The Finished Goods start with the letter "F"
Can you have more than 2 levels(Component B1 is also made of ...)? Yes you can have more than two levels.
The above script is working, but when I drill down into the Finished Product (Product A), I only get the cost of product B & C, I do not get the breakdown of product B. I am using a pivot table to see the explosion. For me to see the breakdown of Product B, I have to select product B as a Father.
Are there a way around this to be albe to start at level one and drill down to lets say level 5?
Thanks for your help so far.
In this case you would have to build the 5 levels starting from finished goods. First you only load the products starting with 'F' and their ChildCode as ChildCode1. Then you load the ChildCode1 products that were previously loaded (where exists(ChildCode1,FatherCode)) renaming FatherCode to ChildCode1 and ChildCode to ChildCode2...
And so on....until you reach the bottom level. Your data model should have 1-N tables linked by theChildCodeN.
Be careful to also rename the Qty and Unit Cost to the same Qty1....(I bet your link between the tables is not only the product code but also the LotNo...)
Then in the chart you would have to build a Drill Down group with the Products and in the expression get the level you are on and put the coresponding QtyN...
It is a complicated calculation and you have to make sure that one of your childcodes is not part of more than one FatherCode. Then you would have to split the total ChildCost depending on the Father's qty...
Why do you have nulls in your table?Shouldn't the ERP distribute the cost?
I will try the above solution you mentioned.
However, I do have several Childcodes that form part of more than on Fathercode. The cost should be based on the qty required to produce 1 unit of a father.
The cost I am using is the last purchase cost, we do not purchase finished goods, as we produce them ourselves. This is why only the components have a cost. What if one of the father codes do have a cost allocated to it (we maybe had to import the finished product), how would I ignore this, and rather use the cost of the children?
Could I maybe email you the full QVD file, for you to understand the complexity?
You can email me the qvd(firstname.lastname@example.org). Are you sure that this is the way to get the correct cost? In production you have more than the cost of the products that are part of the finished good. You have fixed costs(electricity, employees costs,amortization...) that you cannot include into the real cost of the final product(that's why maybe there is another table in your ERP that has the recipe and the real costs distributed).