Iterate Over some data and find a match from one column in another

[ssripat3]

Hello community,

I have the below sample data in my worklfow. I am trying to pick the element to start the iteration process from will depend on the the data from Table 2.

Before, I start. I would like to split data in Next Assembly column in Table 1 when it might have more than 1 record separated by a ';'. The expected output is in Table 1 V2.

I need to either join the data from Table 1 and Table 2 on Part Number and then Union to bring all the data together from Table 1 (Left).

Table 1

Part Number	Parent Part Number
A1001001
B1001001	A1001001
C1001001A	B1001001
D1001001ZA	C1001001A
N90001009B	Z90001009
N5005010A	N90001009B
1EA001100A	N5005010A
1EA801117F
WHT009011	1EA9564321
WHT009011	1EA501732A
WHT009011	1EA801117M;1EA801117F

Table 1 V2

Part Number	Parent Part Number
A1001001
B1001001	A1001001
C1001001A	B1001001
D1001001ZA	C1001001A
N90001009B	Z90001009
N5005010A	N90001009B
1EA001100A	N5005010A
1EA801117F
WHT009011	1EA9564321
WHT009011	1EA501732A
WHT009011	1EA801117M
WHT009011	1EA801117F

Table 2

Part Number

1EA001100A

WHT009011

D1001001ZA

Joined Table

Part Number	Parent Part Number	Right_Part Number
WHT009011	1EA9564321	WHT009011
WHT009011	1EA501732A	WHT009011
WHT009011	1EA801117M;1EA801117F	WHT009011
D1001001ZA	C1001001A	D1001001ZA
1EA001100A	N5005010A	1EA001100A
B1001001	A1001001
A1001001
N5005010A	N90001009B
1EA801117F
C1001001A	B1001001
N90001009B	Z90001009

My objective is to consider the joined data and then use one record from Right_Part Number. For example, 1EA001100A will be considered and then I need to find a match in Part Number column. If I find a match, I need to check if Next Assembly is populated or not. If populated, I need to find that number again in that Part Number column and so on until there is no match found or the Next Assembly is empty.

So in this scenario, considering 1EA001100A the resultant part I need to find would be Z90001009. After finding the part, I need to store this part alongside the part I started the search with.

Part Number Found	Origin Part Number
Z90001009	1EA001100A
1EA801117F	WHT009011
A1001001	D1001001ZA

Can you please let me know if this is possible and if someone could guide me on how to start of the workflow?

I am bad with Iterative Macros and if this needs one, can you point me to some examples?

Thank you

[CoG]

@jerry4422cherry Your response appears to be directly copied from AI output. Sometimes that can work alright, but in this case, Alteryx is barely referenced, and the answer it gave is not very helpful or clear.

@ssripat3 How would you handle a case like "WHT009011" with multiple parent parts? what if each of them has a parent part? How do you choose which line to output since your sample output only has one record related to that part number? This portion of the problem would require an iterative macro to solve, but how you build it depends on the required behavior.

[ssripat3]

@CoG 

In this scenario based on our logic, the Part Number at the highest level would be always the same for WHT009011 regardless of how many different Parent Part number it has in the data. When we traverse up. we will always find "WHT009011" to be mapped to "1EA801117F". I am sorry for not providing better clarification regarding this outlier in my post.

I am also thinking if there is another way of achieving the output in a different way. What if we can store the Part Number at each level of the map until we reach the last match or no match at all.

In case there are multiple different Parent Part Number matches where it ends, I would just do a unique or get the first record from the Output data.

Part Number	Match 1	Match 2	Match 3	Match 4	Match 5	…	Match n
WHT009011	1EA801117F
1EA001100A	N5005010A	N90001009B	Z90001009
D1001001ZA	C1001001A	B1001001	A1001001

Can you let me know which one would be a better approach for this scenario?

[CoG]

This alternative approach is possible (you will still need an iterative macro) + some extra steps.

Ultimately, your approach depends on what you need, from a data perspective, the two-column layout is best from a data perspective (easiest to read, contains all the information you originally needed), but if you need all the intermediary steps in the format above, then that can be accomplished.

[OllieClarke]

@ssripat3 The looping part of this sounds a lot like weekly Challenge #12 (link) have a look at the responses/solution there to help with that logic.

You may want to wrap this iterative macro in a batch macro if you want to go through Table 2 one row at a time...

[ssripat3]

@OllieClarke and @CoG. I tried for a while yesterday to figure out on how to replicate the logic on my sample data. It didn't work as expected. Can either of you further give me some pointers or assist me with the flow?

[CoG · Accepted solution]

Here is a sample workflow to identify the final parent part:

Here is the macro:

[ssripat3]

Thank you for the answer @CoG . It works as expected and I tested with the original data and it returns the output as expected.