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.
Shall I interpret the silence as a sign that this is particularly trivial or particularly challenging? 😄
@F41LTR41N
Your Excel formula did work for me.
Can you give your desired output?
I am no master but maybe can give a shot.
Hello @F41LTR41N ,
is my assumption correct that your workflow is doing fine (correct results), but you are looking to a faster workflow?
For this I recommend the following:
1.) active the AMP engine (works very well for "computation-costy tasks" like JOINs):
In case you do not see this AMP option, you need to upgrade to at least 2020.2
2.) delete the marked SAMPLE tool:
Reason: if the description is correct, you keep 100% of your data anyhow with your SAMPLE tool.
3.) delete the SELECT tool (marked in red) and do the respective configuration (column selection, renaming etc.) directly in the JOIN tool (marked in yellow):
Please let me know whether this helps you and how fast the workflow is now.
Best regards
Phil
Sure. Please let me know if the following example is clear enough.
Input Table:
Row ID | Level |
1 | 1 |
2 | 2 |
3 | 3 |
4 | 3 |
5 | 2 |
Output Table:
Row ID | Level | Parent ID |
1 | 1 | |
2 | 2 | 1 |
3 | 3 | 2 |
4 | 3 | 2 |
5 | 2 | 1 |
Thanks PhilippK. I'll give it a try and get back to you.
The question here is that how do you decide the Parent level.
My workflow takes 1.7 seconds but please check it fits your business requirement.
@Qiu,
The Parent Level will always be equal to the following:
IF [Level] > 1 THEN [Parent Level] = [Level] - 1
However, I'm interested in retrieving the [Row ID] of the parent assembly that material is nested under. In other words, it's a reverse lookup until you hit the first row above for which [Level (parent row)] = [Level (child row)] - 1.
Your workflow seems to solely return the equivalent of [Parent Level] = [Level] - 1, right?
I tweaked the script a bit based on your inputs and it now runs under 14 min. Thanks a lot.
I suppose the only question would be whether there's a more efficient way to write this as an iterative DO WHILE loop, rather than this brute force JOIN approach. Any thoughts?
Hi @F41LTR41N ,
from 30 min to <14 min - that is really awesome!
Appreciated if you mark my post as solution.
Regarding your follow-up question: I recommend that you evaluate the usage of a FIND & REPLACE tool instead of a JOIN tool:
https://community.alteryx.com/t5/Interactive-Lessons/VLookUps-with-Designer/ta-p/80201#done
Video "To Join, or not to Join":
Depending on your data, this can also shorten your workflow cycle time.
Best regards
Phil