Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
DataWhisperer
Contributor II
Contributor II

Return Next Date if Separate Date Field is Higher

Hi All,

I have 2 sets of date fields, date field 1 'Vended Date' (which is when the Meter Number performs an activity), then date field 2 'Last Transaction Date' is the last time the Meter Number was invoiced out. 

So where the Last Transaction Date is higher than the First Vend Date, I need to return the next Vended Date after the Last Transaction Date. So on the below data example, I would need to return 07/27/2020 as my New First Vend Date.

DataWhisperer_0-1739970450353.png

 Below is my script

VendTMP:
NoConcatenate
Load Distinct
Date as [Vended Date],
text([Meter Number]) as [Meter Number],
text([Meter Number])&Date as Key
From Table1

LastVend: 
NoConcatenate
Load
[Meter Number],
LastValue([Vended Date]) as [Last Vend Date],
FirstValue([Vended Date]) as [First Vend Date]
Resident VendTMP
Group By [Meter Number];

Left Join (VendTMP)
Load
"Meter Number",
[First Vend Date] as [First Vend DateTMP]
Resident LastVend;

Left Join (VendTMP)
Load
"Meter Number"&[First Vend Date] as Key,
1 as FirstVendFlag
Resident LastVend;
Drop Table LastVend;

Left Join (VendTMP)
Load
"Meter Number",
[Last Transaction Date] as [Last Transaction DateTMP]
Resident LastInvoiceDetails;


Any ideas?


Labels (3)
1 Reply
morgankejerhag
Partner - Creator III
Partner - Creator III

Something like this?

Data:
LOAD 
[Meter No],
date#([Vended Date],'MM/DD/YYYY') as [Vended Date],
date#([First Vend Date],'MM/DD/YYYY') as [First Vend Date],
date#([Last Transaction Date],'MM/DD/YYYY') as [Last Transaction Date]
INLINE [
    Meter No, Vended Date, First Vend Date, Last Transaction Date
    04267833152, 02/28/2019, 02/28/2019, 11/01/2019
    04267833152, 03/31/2019, 02/28/2019, 11/01/2019
    04267833152, 04/30/2019, 02/28/2019, 11/01/2019
    04267833152, 07/27/2020, 02/28/2019, 11/01/2019
    04267833152, 08/26/2020, 02/28/2019, 11/01/2019
    04267833152, 09/25/2020, 02/28/2019, 11/01/2019
    04267833152, 12/25/2020, 02/28/2019, 11/01/2019
    04267833152, 01/25/2021, 02/28/2019, 11/01/2019
    04267833152, 07/25/2024, 02/28/2019, 11/01/2019
    04267833152, 08/25/2024, 02/28/2019, 11/01/2019
    04267833152, 09/25/2024, 02/28/2019, 11/01/2019
    04267833152, 10/25/2024, 02/28/2019, 11/01/2019
    04267833152, 12/25/2024, 02/28/2019, 11/01/2019
    04267833152, 01/25/2025, 02/28/2019, 11/01/2019
];
 
left join
Load
[Meter No],
FirstSortedValue([Vended Date],[Vended Date]) as [New First Vend Date]
resident Data where [Last Transaction Date] > [First Vend Date] and [Vended Date] > [Last Transaction Date] group by [Meter No];