Find Replace to lookup Topmost match

[Bob]

Hi, I am trying to use Find replace tool to perform lookup. As there are multiple matches in the source file, I want to lookup the topmost value. However, Alteryx is randomly picking any match. How can I set it to pick up the top most match only? 

Thank you for your help!

[PhilipMannering]

Hi @Bob 

I don't think it's randomly picking a match. I think it will prioritize

a. The longest match possible. And then,

b. The bottom most match.

So if you want to prioritize the matches at the top, reverse the order of your look up list by adding a Record ID Tool > Sort Record ID Descending.

Let me know how it goes!

Thanks

Philip

[mceleavey]

Hi @Bob ,

I've created a dataset as follows:

And a lookup table to replace the Product with a new name:

So, we only want to replace the first instance of a product. In this case Product A appears twice, and so we only want to replace the first instance.

To do this, I want to isolate the first instance of each Product. To do this, I sort by the product (ascending or descending doesn't matter), and then by the record ID. Now, the Record ID field might be a date field, or whatever you're using to determine the first instance of something.

I end up with this:

I then take a sample tool and take the first of each product, and another stream which skips the first of each product.

To the first of each product I attach the Find and Replace tool and connect the lookup table to this:

This replaces the product with the new product value. I then simply union the two streams back together and sort by Record ID:

This has now replaced the product value for the first instance of each product.

I hope this helps,

M.

[Bob]

Hi @philipmannering

Thank you for your response. Unfortunately, this solution did not work for me. I have attached an example of my input file and what I am finally hoping to get as output. 

Thanks again for your help!

 

[Sebastiaandb · Accepted solution]

Hi @Bob  ,

Here is the answer:

Just pick the sample tool and set it to group by on the Product No. column.

Credits to the two lats @mceleavey  and @PhilipMannering  tough, your initial question looks to me as it was a bit different from what you actually want to achieve :-). 

Greetings,

Seb

[Bob]

Thanks everyone for your help! It works now :)