Multilevel BOM | Determine Parent ID of Row ID

[F41LTR41N]

What's the most efficient way to calculate the [Parent ID] of a given multilevel bill of material's [Row ID] solely based on the [Level] of said [Row ID] and the first lower level row above it?

For small BOMs one can easily use the following Excel formula, where column B represents [Level] and column A represents [Row ID]:

=IFERROR(LOOKUP(2;1/($B$2:B2=(B2-1));$A$2:A2);"")

However, this takes more than 30 minutes to compute for bills of material > 150,000 rows.

A colleague of mine devised one approach in Alteryx Designer but it also took 31 min. See attached screenshot.

I've hereby attached some sample data of a ~ 150K row bill of material. Please advise.

[Qiu]

@F41LTR41N 
Maybe I did something unnecessary?

Just check the result after the Union Tool?

[F41LTR41N]

@PhilippK,

Well technically the solution doesn't appear to work for the 300K rows I was hoping to address with it. I'm currently getting a message claiming "The allocator would appear to be deadlocked." What does this mean?

Furthermore this FIND and REPLACE function seems to only work for text strings and doesn't appear to let me pick the starting row of the search, nor its direction. Am I missing something?

EDIT: It took 1:53 hours to run once I unselected the AMP engine option. Meanwhile, despite not being responsive for 30 min, Excel gets the same job done in 27% of the time.

[PhilippK]

Regarding your Find & Replace tool question:

yes this is one of the differences between F&R and the Join tool.

Regarding your warning "the allocator would appear to be deadlocked" - looks like this is related to the AMP Engine usage and shortage of hardware resources on your machine:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/The-allocator-would-appear-to-be-deadl...

I think it is worth reaching out to Alteryx Support, as there is no official solution/description of this warning message (or at least I did not found it):

https://community.alteryx.com/t5/Support/bd-p/SupportPage

[F41LTR41N]

Thanks for the clarification on this deadlock error message.

Is there any way for Alteryx to yield the same output as Excel in anywhere the same computational time, e.g. 30 min instead of 113min?

[PhilippK]

Well with the recommendation it takes only 14 min right?

I would like to understand whether this AMP warning is really just a warning or whether your output is broken/incomplete.

Can you check that please? In case the output is fine, that you have a fast workflow already.

Thank you and best regards

Phil

[F41LTR41N]

It worked in 14 min for smaller datasets. For 300,000 rows it took 93 min without AMP. I had to turn off AMP since it would essentially lock up once the deadlock message appeared. There was no output when this deadlock message is issued. You have to manual stop the workflow which stops displaying any progress.

[PhilippK]

I think it is worth to reach out to Support in order to investigate that AMP issue - submit a new case here:

https://community.alteryx.com/t5/Support/bd-p/SupportPage

in case this gets fixed, the workflow performance is better than without AMP.