Message Boards Message Boards


Parts quantities on BOM explosion

Posted 10 years ago
6 Replies
3 Total Likes
Dear Sirs,

I wish I could perform a BOM explosion on Mathematica to calculate the parts quantiites like this VBA excel macro perfom:

So for instance if I have the two first following tables I would like to obtain the third one:

Thanks in advance!!!!
POSTED BY: Edson Ferreira
6 Replies
Posted 10 years ago
Ooo.. Bad programmer.

The smallest modification I can make is to change the last line to
In[2]:= Map[{First[#], List@@Plus@@Expand[ToExpression[First[#]]*Last[#]]}&, z]

Out[2]= {{"MOD001", {5 PA702, 20 R2345, 50 R5544}}, {"MOD002", {6, SA200}}}
Is that sufficient? I am not at all certain that has not broken something else.

Test this carefully before you depend on it. Please let me know if there are any other errors you find.
POSTED BY: Bill Simpson
Posted 10 years ago
Thanks for all replies!!!

I have run the code above and it worked perfectly!

However I miss one precious information! The output table has one missing column in the result I got: the model column (see above).

Without this information I do not know where the lowest level part quantity comes from.
POSTED BY: Edson Ferreira
Posted 10 years ago
"Applied Mathematica: Getting Started, Getting It Done" by Shaw and Tigg is 20 years old now, but it has an information handling orientation and I learned a great deal from that.

Almost any problem in Mathematica can be done at least a dozen different ways, at least a couple of which are completely incomprehensible.

You could turn the BOM information into a tree and walk the tree to count the total items used. You could use pattern matching and repeatedly replace items to create the count. You could use searching or selection to repeatedly extract items from a table. There must be other completely different ways of doing this. I thought about using a vector inner product to calculate the result but didn't spend the time thinking about that to be able to finish it up. It would be possible to create a queue, load the queue with the top level demand items and then repeatedly pull the next item from the queue, if it has children entries in the BOM table insert all those into the queue, otherwise put it into an output pool, until the queue is empty, then perform a sort of Union on the output pool to merge all the quantities for each unique childID. All entries would need a scalar multiplier to indicate the number needed for that particular step in the process. This was actually the idea behind my early attempt at a solution and only part way through did I switch to using := to support the calculations.
POSTED BY: Bill Simpson
This is a good example of an information or data processing problem that involves some minor calculation.  I'm new to Mathematica and have been trying to evaluate how useful it is for information processing as opposed to mathematical calculations.  There are many references to data handling or data manipulation on the Wolfram site but there are few examples of it so it is hard to tell what are real capabilities and what is marketing hype.  This problem makes a good test case for sorting that out.  Is the posted solution  the only way to handle this type of problem in Mathematica?  For data processing problems (that do not involve curated data from Wolfram) are the only options to use the Database Link or to treat the data as Mathematica identifiers?
POSTED BY: Carl Lemp
Posted 10 years ago
With BOM.CSV containing
and Demand.CSV containing
this is one way of trying to calculate your result
 In[1]:= ClearAll["Global`*"];(*Clear all existing assignments*)
 w = Import["BOM.CSV"];
 x = GatherBy[w, First];(*Group entries by ParentID*)
 y = MapThread[SetDelayed, {Map[ToExpression[#[[1,1]]]&, x],
        Map[Plus@@Table[ToExpression[#[[i,2]]]*#[[i,3]], {i,1,Length[#]}]&, x]}];(*Create BOM assignments*)
 z = Import["Demand.CSV"];
 List@@Plus@@Expand[Map[ToExpression[First[#]]*Last[#]&, z]]
 Out[4]= {5 PA702, 20 R2345, 50 R5544, 6 SA200}
Please test this on a smaller, but actual, example before throwing 4000*108000 at it.
Note: This depends on all your item identifiers beginning with a letter and having only letters and digits in the identifier.
Perhaps someone can see a way to make the definition of y half as complicated or find another far simpler method of doing all this.
What y does is evaluate ParentID:=ChildID*ChildQty+ChildID*ChildQty+... for each unique ParentID in the build of materials file.
POSTED BY: Bill Simpson
Posted 10 years ago
Just to give an ideia:

TOP LEVEL DEMAND (TLD) has 4000 parent itens;
BILL OF MATERIALS (BOM) has 108000 parent-child relationships.
POSTED BY: Edson Ferreira
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
or Discard

Group Abstract Group Abstract