Challenge #81: Multi-Level Bill of Materials Problem

[JoeM]

Last week's solution can be found here!

This week's challenge was brought to you by no other than the distinguished @Joe_Mako!

A bill of materials or product structure (sometimes bill of material, BOM or associated list) is a list of the raw materials, sub-assemblies, intermediate assemblies, subcomponents, parts and the subcomponents each needed to manufacture an end product.

In the bill of materials, each record is a line item with a Line ID, Level, Part Number, Description, Unit, Quantity, and Parent ID. Your challenge is to calculate the 'Full Quantity' and 'Parent Quantity' of each line item. For example, 1 car has 4 wheels, each wheel has 5 bolt assemblies, and each bolt assembly has 2 washers. For the washer line item, the Full Quantity is 40, and the Parent Quantity is 20.

The ideal solution would work for any number of levels.

Charlie Chaplin on the Assembly Line

[patrick_digan]

@JoeM @Joe_Mako Fun challenge. 

Spoiler

An iterative macro seemed like the most straightforward way to attack this, so i wanted to see if you could do it without a macro and handle any number of levels. I was able to use the multi-row tools to accomplish this.

I sorted the data from the highest level (4 in this case) to the lowest level. Then I built a field in the multi row formula tool that basically captured all my combinations. For example If I have product 1 connected to product 2 connected to product 3 connected to product 4, then I would generate "-1--2--3--4-" (each product is just encapsulated by a delimiter, otherwise 1234 could be 1 2 3 4 or 12 34). For each row, I add the line id and the parent id and replace if it already exists. For example, the second record with line ID 12 and parent id 11 adds "-11--12-". Then when I get to the row with LineID = 11, it will replace the -11- with "-10--11-" so that when I'm done, I would get a row like "-5--10--11--12". Note that my last record would hold all my combos separated by commas. Then I used the text to column tools to split these out into rows and join it back to my original data. I used a multi-row formula tool to get the parent quantity (since in my example 12 would have to account for the quantities of 5 and 10 and 11).  The formula tool can then get the full quantity. I use a unique tool get just one instance of each Line ID (although some get necessarily duplicated as part of the process, each instance should have the same quantities). 

[estherb47]

Really fun challenge, @Joe_Mako!!

Started to create an Iterative macro, but then remembered techniques from past challenges and decided to do it macro-less. Took a slightly different angle than @patrick_digan

Spoiler

Started with a self join to generate all possible combinations of numbers. Then filtered out only what was needed -- where the original line ID was greater than or equal to the joined line ID, and same for the Level fields. So, record 1, a level one, gives us one combination, record 2 gives us both records 1 & 2, etc.

Sorted these results by Line ID and then Level, ascending, and then removed duplicates.

Next step was to generate the running product within each Line ID. I used a multirow tool, multiplying the current quantity by the running product for the line above, grouped by Line ID.

Used a Summarize to grab the last value for each line ID (the final product for each row), then joined back to the original data set to get all of the information back together.

One more join to the original data set, matching Line ID to Parent ID, to link in the Parent Quantity.

[gnans19]

I took Iterative Macro route.

Thank you @Joe_Mako for the challenge.

@estherb47 @patrick_digan Liked your solutions.

Spoiler

[PhilipMannering]

Similar to @gnans19, and I'm guessing anyone else who uses an iterative macro. See below for solution,

Spoiler

[NicoleJohnson]

My solution! Took the Iterative macro approach, and while it technically worked, I couldn't figure out how to get the iterative results to automatically sort into Line ID order before output... but I was happy enough to get the iterative part working, so it'll do :)

Spoiler

[SeanAdams]

thanks for the challenge @Joe_Mako!

Very similar approach to @gnans19, and @NicoleJohnson and @PhilipMannering but not as slick!

Spoiler

... the difference is that I passed in a "current Level" indicator which then progressively filters the data.    @gnans19, and @NicoleJohnson and @PhilipMannering all used Engine.IterationNumber which is a better approach.

Actual Macro:
- Filter out the rows which are on the current level.    So, if you're on level 1 at the moment, then pass level 1 to the output
- Then take level 1 and join it to every item on level 2 as a parent, and bring down the parent quantity
- Increase the current level number - and send anything with level 2+ to the iterator
...job done

Calling Macro:

[LordNeilLord]

I was going to an iterative macro like the others had done but it seems a bit overkill for the problem at hand, so I went for a more simplistic approach.

Spoiler

[LandonG]

Solution attached.

[ggruccio]

I'm becoming very attached to macros!