Hi, Currently working on a report where I have to find the cash short/over. Basically we have sales data (Table 1) and bank deposits data (Table 2) and trying to find the amount variance. Challenge is Bank deposits data we don't have the sales date. So basically we don't have any fields which we can use to join them. The logic is we have to find the nearby date and nearby value. Below is sample data- Table1Table 2 Sale DateCash CollectedDeposit Date Bank Deposit Variance2019-06-03199.082019-06-07199.62 0.542019-06-04147.992019-06-07147.97 -0.022019-06-051292019-06-07129.8 0.802019-06-06180.832019-06-07180.94 0.112019-06-07177.942019-06-10171.82 -6.122019-06-08166.192019-06-10165.71 -0.482019-06-09219.752019-06-10219.16 -0.59 1220.78 1215.02 -5.76 Sale DateCash CollectedDeposit Date Bank Deposit Variance2019-06-10117.62019-06-14114.82 -2.782019-06-11125.732019-06-14128.24 2.512019-06-12137.62019-06-14137.61 0.012019-06-13183.182019-06-14183.79 0.612019-06-14161.262019-06-17161.15 -0.112019-06-15247.712019-06-17248.92 1.212019-06-16205.442019-06-17199.93 -5.51 1178.52 1174.46 -4.06 Any help would be appreciated. Regards, Irfan

Join 2 Data sources with nearby value

Hi,

Currently working on a report where I have to find the cash short/over.

Basically we have sales data (Table 1) and bank deposits data (Table 2) and trying to find the amount variance.

Challenge is Bank deposits data we don't have the sales date. So basically we don't have any fields which we can use to join them.

The logic is we have to find the nearby date and nearby value.

Below is sample data-

Table1	Table 2
Sale Date	Cash Collected	Deposit Date	Bank Deposit	Variance
2019-06-03	199.08	2019-06-07	199.62	0.54
2019-06-04	147.99	2019-06-07	147.97	-0.02
2019-06-05	129	2019-06-07	129.8	0.80
2019-06-06	180.83	2019-06-07	180.94	0.11
2019-06-07	177.94	2019-06-10	171.82	-6.12
2019-06-08	166.19	2019-06-10	165.71	-0.48
2019-06-09	219.75	2019-06-10	219.16	-0.59
	1220.78		1215.02	-5.76

Sale Date	Cash Collected	Deposit Date	Bank Deposit	Variance
2019-06-10	117.6	2019-06-14	114.82	-2.78
2019-06-11	125.73	2019-06-14	128.24	2.51
2019-06-12	137.6	2019-06-14	137.61	0.01
2019-06-13	183.18	2019-06-14	183.79	0.61
2019-06-14	161.26	2019-06-17	161.15	-0.11
2019-06-15	247.71	2019-06-17	248.92	1.21
2019-06-16	205.44	2019-06-17	199.93	-5.51
	1178.52		1174.46	-4.06

Any help would be appreciated.

Regards,

Irfan

Date Time

Fuzzy Match

Join

Accepted answers

danilang

Hi @Irfan

I had to change the way the Closest Amount macro worked to remove a value from the deposit list once it was used.

Dan

Main v2.yxzp

All comments

danilang

Hi @Irfan

Here's a workflow that does what you're looking for

It uses 2 iterative macros. The first one finds the correct deposit date for each of the sales dates. The second one compares each sale to all corresponding deposits on correct deposit date and assigns the one where the variance is the smallest

Here are the results

Dan

Main.yxzp

Irfan

Thank You so much Dan.

I appreciate your help.

It's working but for couple of date range getting issues, same bank deposits joining twice with sales. See attached screen shot.

Result should be like-

Sale Date	Cash Collected	Deposit Date	Bank Deposit	Variance
2019-05-20	156.76	2019-05-24	156.73	(0.03)
2019-05-21	157.2	2019-05-24	158.34	1.14
2019-05-22	175.49	2019-05-24	175.45	(0.04)
2019-05-23	136.58	2019-05-24	136.55	(0.03)
2019-05-24	149.2	2019-05-28	149.63	0.43
2019-05-25	191.47	2019-05-28	132.14	(59.33)
2019-05-26	118.93	2019-05-28	118.93	-

Capture.PNG

danilang

Hi @Irfan

I had to change the way the Closest Amount macro worked to remove a value from the deposit list once it was used.

Dan

Main v2.yxzp

Geoowl

Thanks for posting @Irfan - It's an interesting problem.

Just adding thoughts to @danilang's solution which looks great:

- You may want to build in a threshold whereby some records will simply be left unassigned (if the variance is very high and/or days between deposit and sales date is large).

- Currently solution is based on variance only, whereas a weighting to also use difference between dates might help (i.e. variance of 10 days and $1 in cash is perhaps a worse match than an exact match on date and a variance of $1.5). If you wanted to include, simply calculating datetimediff in days and creating a scored ranking combining this and variance and finally sorting on this gives you a combination of the two. You could also produce a %match column based on this, which would also allow you to filter for matches below a threshold in response to first point. Of course, weighting between these two is more of an art than science.

Best of Luck,

Matt

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703