Bill of Materials challenge

[Krzysztof_Hyla_1986]

Hi, I have simple BOM table with Component Item # & Parent Item # columns. What  I would like to achieve is to add two columns: one with highest level item # for each component and second one with relative level in the BOM hierarchy. Any ideas how to approach this?

Of course I only listed one BOM example, but the list of finished items can go from A, B, C, up to Z.

[csmith11]

There are more simply approaches. But I tried to consider what if there were more than 1 letter in the prefix.

[JarekSkudrzyk]

@Krzysztof_Hyla_1986 I solved using iterative macro - please check out the attached solution.

[Jonathan-Sherman · Accepted solution]

Hi @Krzysztof_Hyla_1986,

The use case you're trying to solve here is very similar to one of the weekly challenges (#81), so I would definitely take a look on there as there's so many different approaches! Personally i'd go down the iterative macro route.

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-81-Multi-Level-Bill-of-Materials-Problem...

Kind regards,

Jonathan

[Ben_H]

Hi @Krzysztof_Hyla_1986,

I'm not sure if I've oversimplified or misunderstood something but you could do it with a single formula tool.

1. I used Regex to drop the numbers from the Parent Item to get Finished Item

2. Use regex to drop the Letters from Component Item, take the length and add 1

3. The select tool just reorders the fields

EDIT: forgot to attach the sample

Regards,

Ben

[Krzysztof_Hyla_1986]

This solution actually won't work in my case as the part numbers in my real database are totaly random, like '9864649878-570P'. I just simplified the example to make the understanding of the question a little bit easier.