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

Joining Approximate Matches

kevin-lindorfer
7 - Meteor

Hello!

 

I am working to match items from a bank statement to sales for a bank reconciliation. As with most places handling cash, the amounts of the deposits do not always match the amount of sales due to cash over/short, and the lag time at the bank may cause there to be a 2-4 day difference in date.

 

I am able to find many matches by Joining with Location ID, Amount, and Date. All of these must be exact though for them to match.

 

I am trying to find a way to make the following data join together, within say $5 and within 5 days to create approximate match. Fuzzy Match could be a helpful tool here, but I am not sure.  

 

Data 1:

DateAmountLocation IDDescription
7/27/201775.00LASSales
8/01/20171500ATLSales
8/03/2017457.91DALSales
8/04/2017150STLSales

 

Data 2:

DateAmountLocation IDDescription
8/01/201775.00LASCash Deposit
8/04/20171498.97ATLCash Deposit
8/08/2017457.93DALCash Deposit
8/27/2017150STLCash Deposit

 

In the ideal world, the output would look like this:

DateAmountLocation IDDescriptionDateAmountLocation IDDescription
7/27/201775.00LASSales8/01/201775.00LASCash Deposit
8/01/20171500.00ATLSales8/04/20171498.97ATLCash Deposit
8/03/2017457.91DALSales8/08/2017457.93DALCash Deposit

 

I would definitely need the Location ID to match perfectly, but is there a way to build this logic into a join tool using formulas or some other tool to get those amounts to show as matches? I would not want the last record with STL to match because it is outside of the date range set. 

 

I also attached a flow with sample data if this helps.

 

Thank you!!!

4 REPLIES 4
BenMoss
ACE Emeritus
ACE Emeritus

Here's my attempt. Loved this challenge btw.

 

I rounded values to the closest 50 and then did a +4 days on the sale date.

If not perfect it should definitely help you get to where you wish to be.

 

Ben

BenMoss
ACE Emeritus
ACE Emeritus

I've generated a workflow that instead of rounding values to 50, works on the +-5 logic you outlined in your post. I missed this.

 

Ben

kevin-lindorfer
7 - Meteor

This works perfect for what I need it for! Thank you SO much!

sanchezj
5 - Atom

thanks for this solution! it looks the logic is just +5 (Could you add the -5 logic)?

 

if a date is 27th August, it should be counted from the margin of +/-5 i.e., 22nd August and 1st October? 

 

Is there other method that does not create new rows? it would be heavy computational cost if you consider big datasets. I want to join two data sources on the time-stamp with a margin of 3 minutes i.e., +/-3. 

 

Thanks.

Labels