Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

One sided join?

do55
6 - Meteoroid

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.

 

5 REPLIES 5
Emil_Kos
17 - Castor
17 - Castor

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. 

Want to learn more about Alteryx? | Checkout Alteryx Online Course with Certification which helps you to accelerate your work. Join Now: https://bit.ly/3oKNvHS In this video, we will learn very quickly about How to use the Find and Replace Tool in Alteryx with the help of a dictionary. The Tool ...
AngelosPachis
16 - Nebula

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
6 - Meteoroid

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
17 - Castor
17 - Castor

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
17 - Castor
17 - Castor

Hi @do55,

 

Thank you for marking my post as an answer!

 

Good luck!

Labels