Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a set of data as mentioned below even if the Id "1" is present for different month is always give 2099-12-31 as End date
Temp:
load ID , MonthName(Timestamp(timestamp#(Date,'M/D/YYYY h:mm:ss[.fff]'),'M/D/YYYY h:mm:ss[.fff]')) as Month,
(Timestamp(timestamp#(Date,'M/D/YYYY h:mm:ss[.fff]'),'M/D/YYYY h:mm:ss[.fff]')) as Date;
LOAD * Inline [
ID, Date
1,12/30/2016 16:00:04
1,1/31/2017 16:00:04
1,2/28/2017 16:00:05
];
inner join (Temp)
load *,date(monthstart(maxdate),'DD/MM/YYYY') as MonthStart;
load ID ,(max(Date)) as maxdate
Resident Temp GROUP by ID,Month
Order by ID, Date desc;
Temp2:
NoConcatenate
load * resident Temp order by ID ,Date desc;
drop table Temp;
Final:
load ID,
Floor(MonthStart) as StartDate,
Floor(date(If(ID = Peek(ID), Peek(MonthStart)-1,Floor('2099-12-31')),'DD/MM/YYYY')) AS EndDate
Resident Temp2;
drop table Temp2;
So if you use peek, you need to use StartDate which is a Field in Final Table.
Floor(date(if(ID=peek(ID),Peek( StartDate )-1, Floor('2099-12-31')),'DD/MM/YYYY')) AS EndDate,
What is your expected output here?
rest of dates
What is expected output?
Is it previous value you are trying to find With peek?
Maybe this help you?
Floor(date(If(ID = Previous(ID), previous(MonthStart)-1,Floor('2099-12-31')),'DD/MM/YYYY')) AS EndDate,
Not sure what you mean. Can you list out the output?
can u plz explain why previous gives the correct result instead of peek
I dont know if it gives you correct result, I dont know what output you expect. But read this.
Peek() will address the output table records -- and MonthStart field is not part of your 'Final' table output records.
So if you use peek, you need to use StartDate which is a Field in Final Table.
Floor(date(if(ID=peek(ID),Peek( StartDate )-1, Floor('2099-12-31')),'DD/MM/YYYY')) AS EndDate,