Incorrect Value with Peek

[Anonymous]

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;

[sunny_talwar]

What is your expected output here?

[Anonymous]

rest of dates

[stabben23]

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,

[sunny_talwar]

Not sure what you mean. Can you list out the output?

[Anonymous]

can u plz explain why previous gives the correct result instead of peek

[stabben23]

I dont know if it gives you correct result, I dont know what output you expect. But read this.

Peek() or Previous() ?

[swuehl]

Peek() will address the output table records -- and MonthStart field is not part of your 'Final' table output records.

[stabben23 · Accepted solution]

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,