Nearest Next Date

[rvc_121985]

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,

[justISO · Accepted solution]

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.

[rvc_121985]

Hi JustISO,

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

Thanks,

[marcus_sommer]

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.

[Kushal_Chawda · Accepted solution]

@rvc_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;

[rvc_121985]

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

[marcus_sommer]

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

[rvc_121985]

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

[Kushal_Chawda]

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

[MarcoWedel · Accepted solution]

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/ScriptPref...