topic Re: Nearest Next Date in App Development

Nearest Next Date

RC_121985 — Fri, 21 Apr 2023 09:16:41 GMT

Hi Team,

Looking for the Script to pick nearest next Date from the list of dates. I have below example to show. in MM/DD/YYYY

Table A: Invoices with Due Dates

Invoice	Due date
1234	3/31/2022
5678	1/30/2022
9012	12/20/2022
3456	12/31/2022

Table B : Payment Dates

Payment Date

1/31/2022

3/30/2022

4/5/2022

12/30/2022

1/2/2023

For each invoices from Table A, I would like to pick next Nearest Payment date from Table B.

Result should appear as below;

Invoice	Due date	Next Nearest Pmnt Date
1234	3/31/2022	4/5/2022
5678	1/30/2022	1/31/2022
9012	12/20/2022	12/30/2022
3456	12/31/2022	1/2/2023

Is there any way to do this in Script ?

Thanks,

Re: Nearest Next Date

justISO — Fri, 21 Apr 2023 11:16:55 GMT

Hi, not so elegant solution, but you can try something like this:

TableA: LOAD * inline [ Invoice, Due date 1234, 2022-03-31 5678, 2022-01-30 9012, 2022-12-20 3456, 2022-12-31]; TableB: LOAD * inline [ Payment Date 2022-01-31 2022-03-30 2022-04-05 2022-12-30 2023-01-02]; NoConcatenate main_temp: LOAD Invoice, [Due date] RESIDENT TableA; JOIN LOAD [Payment Date] as [Due date], [Payment Date] RESIDENT TableB; DROP TABLES TableA, TableB; NoConcatenate main_temp2: LOAD Invoice, [Due date], if(isnull([Payment Date]), peek([Payment Date]), [Payment Date]) as [Payment Date] RESIDENT main_temp ORDER BY [Due date] DESC; //sorting backwards to peek NoConcatenate Main: LOAD Invoice, [Due date], [Payment Date] as [Next Nearest Pmnt Date] RESIDENT main_temp2 WHERE not isnull(Invoice) ORDER BY [Due date] ASC; //get back sorting DROP TABLES main_temp, main_temp2;

Basically, you make a list of all possible dates, sorting them descending and with peek() (or previous()) find nearest date for invoice.

Re: Nearest Next Date

RC_121985 — Fri, 21 Apr 2023 11:58:11 GMT

Hi JustISO,

Good Solution. If I do not find any quick fix then, it is only the solution I believe.

Thanks,

Re: Nearest Next Date

marcus_sommer — Fri, 21 Apr 2023 13:43:39 GMT

Your example doesn't look complete because a payment-table just with dates and without any key to the invoices makes not much sense. But if there is such key the payment-table might be aggregated to the max date - so it might be look like:

t: load InvoiceID, InvoiceDate from Invoices;
left join(t) load InvoiceID, date(max(PaymentDate)) as PaymentDateMax from Payments
group by InvoiceID;

Another approach might be just to rank the payment-dates, for example:

t: load InvoiceID, PaymentDate,
if(InvoiceID = previous(InvoiceID), peek('Rank') + 1, 1) as Rank
resident Invoices order by InvoiceID, PaymentDate desc;

and then Rank could be used as dimension, selection or set analysis condition.

Re: Nearest Next Date

Kushal_Chawda — Fri, 21 Apr 2023 14:03:46 GMT

@RC_121985 try below as well

TableA: LOAD * inline [ Invoice, Due date 1234, 2022-03-31 5678, 2022-01-30 9012, 2022-12-20 3456, 2022-12-31]; TableB: LOAD * inline [ Payment Date 2022-01-31 2022-03-30 2022-04-05 2022-12-30 2023-01-02]; Dates: Load date(FieldValue('Due date',RecNo())) as [Due date] AutoGenerate FieldValueCount('Due date'); join(Dates) Load date(FieldValue('Payment Date',RecNo())) as [Payment Date] AutoGenerate FieldValueCount('Payment Date'); Left Join(TableA) Load [Due date], date(min([Payment Date])) as [Next Nearest Payment Date] Resident Dates where [Payment Date]>[Due date] Group by [Due date]; Drop Table Dates;

Re: Nearest Next Date

RC_121985 — Fri, 21 Apr 2023 14:15:32 GMT

No There is no key. Because It is just the dates of the Payments executed.

Re: Nearest Next Date

marcus_sommer — Fri, 21 Apr 2023 14:20:04 GMT

I don't believe that there is no relation between this information. It would lead to a single max. date from the payments against all invoices ...

Re: Nearest Next Date

RC_121985 — Fri, 21 Apr 2023 14:39:19 GMT

@Kushal_Chawda

Thanks for the script. In fact Result is multiplied no of time the payment dates. I don't think it is working.

Regards,

Re: Nearest Next Date

Kushal_Chawda — Fri, 21 Apr 2023 14:49:01 GMT

@RC_121985 not sure how it is multiplying as you are joining single next payment date with due date. make sure that you are joining with tableA on Due Date.

Re: Nearest Next Date

MarcoWedel — Fri, 21 Apr 2023 17:45:22 GMT

another solution might be:

tabInvoices: LOAD * Inline [ Invoice, Due date 1234, 3/31/2022 5678, 1/30/2022 9012, 12/20/2022 3456, 12/31/2022 ]; tabPayments: LOAD * Inline [ Payment Date 1/31/2022 3/30/2022 4/5/2022 12/30/2022 1/2/2023 ]; Join (tabInvoices) IntervalMatch ([Due date]) LOAD Distinct Alt(Previous([Payment Date]),0) as PrevPaymentDate, [Payment Date] as NextNearestPmntDate Resident tabPayments Order By [Payment Date];

In case there is a CustomerID to tie invoices and payments to, you could use the extended IntervalMatch prefix as well:
https://help.qlik.com/en-US/sense/February2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/IntervalMatch.htm

Re: Nearest Next Date

RC_121985 — Sat, 22 Apr 2023 15:49:44 GMT

@Kushal_Chawda right If I drop the TableB it's Good.

Re: Nearest Next Date

RC_121985 — Mon, 24 Apr 2023 10:05:25 GMT

@MarcoWedel Some how the result is not correct where the same due date is repeating. or more than 2 due days falling on Payments dates are getting 2 different Nearestduedate

Thanks,