cancel
Showing results for
Did you mean:
Announcement | Get certified today - take the Alteryx Designer Core and Advanced exams on-demand now!
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.

## Challenge #81: Multi-Level Bill of Materials Problem

Director, Customer Enablement

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

Aurora

@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).
Quasar

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.

Alteryx Certified Partner

I took Iterative Macro route.

Thank you @Joe_Mako for the challenge.

Spoiler

Highlighted
Alteryx Certified Partner

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

Spoiler

Pulsar

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
Aurora

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:

Alteryx Certified Partner

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
Asteroid

Solution attached.

Bolide

I'm becoming very attached to macros!