topic Re: Matching Orders and Payments Within the Same Time Frame in App Development

Matching Orders and Payments Within the Same Time Frame

LoKi_asterix — Fri, 15 Nov 2024 20:53:39 GMT

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.

Re: Matching Orders and Payments Within the Same Time Frame

Kushal_Chawda — Fri, 09 Aug 2024 19:52:17 GMT

@LoKi_asterix please share example data with expected output?

Re: Matching Orders and Payments Within the Same Time Frame

LoKi_asterix — Fri, 09 Aug 2024 20:51:31 GMT

@Kushal_Chawda See attached, thanks.

Re: Matching Orders and Payments Within the Same Time Frame

Kushal_Chawda — Sat, 10 Aug 2024 20:14:19 GMT

@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.

Re: Matching Orders and Payments Within the Same Time Frame

LoKi_asterix — Sun, 11 Aug 2024 22:00:37 GMT

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

Re: Matching Orders and Payments Within the Same Time Frame

Kushal_Chawda — Mon, 12 Aug 2024 00:00:54 GMT

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