Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #81: Multi-Level Bill of Materials Problem

JoeM
Alteryx Alumni (Retired)

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
17 - Castor
17 - Castor

@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
15 - Aurora
15 - Aurora

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.

image.png


 

gnans19
11 - Bolide

I took Iterative Macro route.

 

Thank you @Joe_Mako for the challenge.

@estherb47 @patrick_digan Liked your solutions.

 

Spoiler
Macro.pngWorkflow.png

 

 

 

PhilipMannering
16 - Nebula
16 - Nebula

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

 

 

 

Spoiler
WC81.pngWC81m.png

 

NicoleJohnson
ACE Emeritus
ACE Emeritus

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
WeeklyChallenge81.JPG
SeanAdams
17 - Castor
17 - Castor

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
2017-10-09_8-39-52.png
Calling Macro:
2017-10-09_8-40-21.png

LordNeilLord
15 - Aurora

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
Weekly Challenge 81.png
LandonG
8 - Asteroid

Solution attached.

ggruccio
ACE Emeritus
ACE Emeritus

I'm becoming very attached to macros!