Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Something like this?