One sided join?

[do55]

This is quite a simple task in Excel that I'm trying to replicate with difficulty in Alteryx.

I have two data sets: invoices - 100 records, payments 10,000 records. Made up numbers but to give an idea of magnitude. 

My aim is to verify that for all 100 invoices a corresponding payment was made. To do this I tried performing a join on as follows:
L - Payments (Payment Amount, Date, Client Name) - 10,000 records

R - Invoices (Payment Amount, Date, Client Name) - 100 records.

However my J has more than 100 results because the L file has lots of near duplicates (and no easy way to delete them before the join). 

Is there a way of doing a one sided join? I.e only for the 100 R's look for a corresponding L and then stop looking. 

That way the maximum number of results is 100. 

In Excel I acheived this using VLOOKUPS/INDEXMATCHES but would like to do this more elegantly in Alteryx.

[Emil_Kos]

Hi @do55,

You might look into the find and replace tool:

https://www.youtube.com/watch?v=-sDDolJ57Y8&ab_channel=YodaLearning-Excel%2CExcelVBAandPowerBI

this will not create any duplicates and it is really simple to use. 

I have attached an example. 

[AngelosPachis]

Hi @do55 ,

What if you used a summarize tool before the L input anchor of your join to remove the duplicate records? Grouping on all three fields would remove any duplicate records I believe.

[do55]

Thanks, I looked at F&R in the first instance but it only seems to look at one field at a time? 

Have you any examples where it will F&R using three fields as a condition?

[Emil_Kos · Accepted solution]

Hi @do55,

You can concantenate those columns [Column1]+[Column2]+[Column3].

Alternatively, you can use sample formula as suggested by @AngelosPachis. I think it will be more efficient way of solving this. 

[Emil_Kos]

Hi @do55,

Thank you for marking my post as an answer!

Good luck!