Alteryx: prevent reuse of matched records after Join (one-to-one matching)

Question

I have two datasets (Day 1 and Day 2) joined in Alteryx with multiple matching conditions.

After the Join, one Day 2 record can match multiple Day 1 records, but I need to ensure that:

* each Day 2 record is used only once

* the first (best) match keeps it

* subsequent matches are discarded

The best match is determined by sorted priority (date difference, document number).

What is the recommended Alteryx pattern to enforce this one-to-one match?
Multi-Row Formula after sorting? Iterative macro? Another approach?

Looking for a scalable and deterministic solution.

alexnajm · Answer

@belensendot can you mark one or more of the responses as a solution to close out this thread? Thank you in advance!

Lada_R · Answer

I would try to make a key from the  multiple matching conditions, and then to use Find Replace instead of Join. Watchout: your sorted priority in the source table should be from worst to best, as you are having in the result the last match, not the first.

jrlindem · Answer

@belensendot  I think @Qiu's method of using the Sample Tool is an easy and scalable way to do what you need.  There are other options though too.  You could also consider using your criteria to "flag" the "best" match and then use a Filter Tool to narrow down the results to just those single values.  Once you have 1:1 then you can perform your Join (or Find Replace) to merge the data together.

Here's an example of each scenario (and attached):

Hope this helps!  -Jay

jrlindem_example.yxmd