How to get latest replacement product when there are multiple replacements?

[RBF]

Hi

I have data in this format.  It tells me what the product code is and what new code has replaced that product:

Product	Replaced By
1000001	1000003
1000002	1000004
1000003	1000005
1000004	1000010
1000005	1000007
1000006	1000008
1000007	1000008
1000008
1000009	1000011
1000010
1000011

As you can see sometimes a product is replaced many times e.g. 1000001 is replaced by 1000003 which is replaced by 1000005, and so on until we get to the last product 1000008 which has no replacement.

Alas, the numbers do not always increase with each step (normally they do but I have a tiny % where the replacement code is a lower  lower number than the original). 

What I want to get is a result that tells me what the very, very last replacement is in the chain, like this:

Product	Replaced By	Final Replacement
1000001	1000003	1000008
1000002	1000004	1000010
1000003	1000005	1000008
1000004	1000010	1000010
1000005	1000007	1000008
1000006	1000008	1000008
1000007	1000008	1000008
1000008
1000009	1000011	1000011
1000010
1000011

How could I do this in the desktop designer?

[Qiu · Accepted solution]

@RBF 
It comes to me that your question is actually a question of building hierarchy.

We can borrow the Hierarchy Generation macro here and make a flow as below

https://community.alteryx.com/t5/Engine-Works/Building-a-Hierarchy-With-Kevin-Bacon/ba-p/453715

[RBF · Accepted solution]

I will try it!

[RBF]

Hi!  Okay this is brilliant and absolutely works with clean data!  

But... what if you have bad data and it makes a loop?  How can I make it give an error or add some sort of cycle breaking?

For example, if 1000008 was replaced by 1000006 but 1000006 says that its replaced by 1000008?

[ChrisTX]

The attached workflow uses the Make Group tool to identify the relationships between [Product] and [Replaced By].

 

Chris