Payment credited one day, same or similar amount out the next day

[Harrydickinson]

Hi all,

I would like to know if it is possible to highlight values that are paid into a customers account on one day, and the same or similar amount is paid out a day later.

I have the following fields:

Transaction value (e.g. 100 USD)

Inward or outward (credit or debit)

Customer name  

Date of payment 

Can anyone help on this one?

[chrismarlow]

Hi,

So you can, below toy script (note DD/MM/YYYY date format) shows how to start by identifying candidate matches. it raises the immediate questions - what is similar & what would you do if you have 2 (or more) candidate matches;

Cheers,

Chris.

data:
LOAD * INLINE [
    TransID, PaymentDate, Customer, DRCR, Value
    1, 01/01/2019, a, dr, 10
    2, 01/01/2019, b, dr, 10
    3, 02/01/2019, a, cr, 11
    4, 02/01/2019, b, dr, 10
];

left join (data)
Load
	TransID AS MatchTransID,
	PaymentDate-1 AS PaymentDate,
	Customer,
	If(DRCR='cr','dr','cr') AS DRCR,
	Value AS MatchValue
Resident data;