Find Replace to lookup Topmost match

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!

Workflow

Accepted answers

Sebastiaandb

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

Firstrow.yxmd

All comments

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,

Find and Replace First.yxmd

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!

Screenshot 2022-04-20 at 10.57.36 AM.png

Sebastiaandb

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

Firstrow.yxmd

Bob

Thanks everyone for your help! It works now

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703