topic Re: Return Next Date if Separate Date Field is Higher in App Development

Return Next Date if Separate Date Field is Higher

DataWhisperer — Wed, 19 Feb 2025 13:09:47 GMT

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.

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?

Re: Return Next Date if Separate Date Field is Higher

morgankejerhag — Sat, 22 Feb 2025 05:31:05 GMT

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];