One sided join?

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.

Developer

Accepted answers

Emil_Kos

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.

All comments

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.

Find and replace.yxmd

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?

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335