Matching Orders and Payments Within the Same Time Frame

[LoKi_asterix]

So essentially, I have an order table and a payment table, both connected by the store ID and Order ID. Payments aren't always made on the same day the order is placed, so the order date and payment date can differ significantly.

My goal is to align orders and payments within the same period under review. For example, if I'm examining orders within 2024, I want to see their corresponding payments within that same timeframe.

I'd appreciate it with a working script.

Thank you.

[Kushal_Chawda]

@LoKi_asterix  please share example data with expected output?

[LoKi_asterix]

@Kushal_Chawda See attached, thanks.

[Kushal_Chawda · Accepted solution]

@LoKi_asterix  try below. Interval match between Order and Payment is on OrderID & StoreID, hence make sure that Name of both fields are same in both the tables.

Payment:
LOAD
RECEIPT_DATE,
RECIEPT_NO,
STORE_ID,
ORRDER_ID,
PAYMENT_AMOUNT
FROM [lib://DataFiles/payments.qvd]
(qvd);

Order:
LOAD
TRANS_DATE,
STORE_ID,
ORRDER_ID,
CLIENT_ID,
PURCHASE_AMOUNT
FROM [lib://DataFiles/Order.qvd]
(qvd);

Left Join(Order)
LOAD date(max(RECEIPT_DATE)) as max_reciept_date,
STORE_ID,
ORRDER_ID
Resident Payment
Group by STORE_ID,
ORRDER_ID;

Inner Join (Order)
IntervalMatch ( RECEIPT_DATE, ORRDER_ID,STORE_ID )
LOAD TRANS_DATE,
max_reciept_date,
ORRDER_ID,
STORE_ID
Resident Order;

Inner join (Order)
Load *
Resident Payment;

Drop Table Payment;

If results are not correct then remove highlighted part in red and keep synthetic key.

[LoKi_asterix]

I appreciate the responses, but the output wasn't as expected, could you kindly review the code again?

[Kushal_Chawda]

@LoKi_asterix  Looks like your date is not in proper date format. First convert it to proper date format using Date#