Challenge #81: Multi-Level Bill of Materials Problem

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

challenge_81_start_file.yxmd

challenge_81_solution.yxzp

Macros

Join

Preparation

Advanced

Intermediate

Interface

Accepted answers

All comments

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

challenge_81_start_file.yxmd

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.

challenge_81_EHB_completed.yxmd

gnans19

I took Iterative Macro route.

Thank you @Joe_Mako for the challenge.

@estherb47 @patrick_digan Liked your solutions.

Spoiler

challenge_81_Gnans.yxmd

IterativeMacro.yxmc

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

challenge_81_NicoleJohnson.yxmc

Challenge_81Test_NicoleJohnson.yxmd

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

challenge_81_LNL.yxmd

LandonG

Solution attached.

challenge_81_start_file.yxzp

ggruccio

I'm becoming very attached to macros!

challenge_81_gg_finish.yxzp

samN

I tried for too long to get a non iterative approach. Wasnt as complete as i would have liked so i gave in.

Solution attached.

Took me quite a while to complete this challenge. I wanted to find a dynamic solution for any number of levels, and struggled to do it for more than 3 levels without an iterative macro. At the end I went for an iterative macro, which was a variation of a macro I did in challenge #12.

I also checked other's non-iterative solutions with an altered input of up to 8 level of BoM and solutions by @patrick_digan and @EstherB47 handled that properly. Impressive! Definitely going to study them closely!

Spoiler

challenge_81_Natasha.yxzp

jamielaird

This one really puzzled me, so I worked through @estherb47's solution until it fell into place.

Spoiler

challenge_81_solution_JL.yxmd

JosephSerpis

Challenge Completed

Challenge_81_Joe_Serpis.yxzp

kelly_gilbert

I definitely need more practice with iterative macros, but eventually figured it out! Pretty similar to the given solution.

challenge_81_solution_klg.yxzp

justin_winter

Here's my attempted solution. Interesting to see the different ways to pass in either the starting element or all data and then either filter within the iterative macro or filter beforehand. For some reason it makes more sense in my head to filter outside of the macro and have the data also in the macro. Maybe bad design (less dynamic), but it works in my head better for now!

challenge_81_solution_jaw.yxzp

daniel_ferreira

Follow my solution package attached...

challenge_81_solved.yxzp

StevenBetts

Here's my solution

Spoiler

challenge_81_start_file_SMB.yxmd

JonM

Having never worked in Manufacturing, this challenge was quite hard for me to get my head around, however I eventually figured out the trick! Thanks

Spoiler

Iteractive Macro!

SOL_challenge_81_start_file.yxzp

kat

Spoiler

Always good to prcatice iterative macro skills
Challenge #81 - macro.PNG

Challenge #81.yxzp

danilang

Woohoo. 1st iterative macro!

I set this one aside after trying it about a month ago, when I first started Alteryx. While I've developed many recursive solutions in my time, they were always in procedural languages. This is the first one I've tried in a declarative language. I had trouble mapping the looping,data set reduction and stop conditions that I was familiar with to their Alteryx equivalents.

A little patience and a lot of practice and I know how many wheel washers go on a car!

Spoiler

Dan

challenge_81_solution_DL.yxzp

KOBoyle

Solution attached. I took the macro approach as this seemed like the real-life approach to a real-life problem. I was interested to see if any of the standard workflow approaches accomplished a flexible (i.e. variable number of levels), recursive join. The solutions using multiple Join tools appeared to be hard-coded to four levels. Other solutions appeared to assume that the Line Id had intelligence were always in a particular order, and possibly that a single part did not appear in multiple BOMs. The solution from @patrick_digan appears to accomplish a flexible, recursive join, but it was very hard to follow and I'm not sure the concatenated List field could handle any volume of BOMs. I would be interested to know if a standard SQL recursive query can be accomplished in a standard Alteryx workflow.