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!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Multilevel BOM | Determine Parent ID of Row ID

F41LTR41N
6 - Meteoroid

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.

16 REPLIES 16
F41LTR41N
6 - Meteoroid

Shall I interpret the silence as a sign that this is particularly trivial or particularly challenging? 😄

Qiu
20 - Arcturus
20 - Arcturus

@F41LTR41N 
Your Excel formula did work for me.

Can you give your desired output?
I am no master but maybe can give a shot.

PhilippK
Alteryx Alumni (Retired)

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

screenshot.png

In case you do not see this AMP option, you need to upgrade to at least 2020.2

 

2.) delete the marked SAMPLE tool:

screenshot2.PNG


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

screenshot3.PNG


 

Please let me know whether this helps you and how fast the workflow is now.

Best regards
Phil

F41LTR41N
6 - Meteoroid

Sure. Please let me know if the following example is clear enough.

 

Input Table:

 

Row IDLevel
11
22
33
43
52

 

Output Table:

 

Row IDLevelParent ID
11 
221
332
432
521

 

F41LTR41N
6 - Meteoroid

Thanks PhilippK. I'll give it a try and get back to you.

Qiu
20 - Arcturus
20 - Arcturus

@F41LTR41N 

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.

1109-F41LTR41N.PNG

F41LTR41N
6 - Meteoroid

@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?

F41LTR41N
6 - Meteoroid

@ PhilippK,

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?

 

F41LTR41N_0-1604949161916.png

 

PhilippK
Alteryx Alumni (Retired)

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":

screenshot.png

 

Depending on your data, this can also shorten your workflow cycle time.

 

Best regards

Phil

Labels