topic Re: Incorrect Value with Peek in QlikView

Incorrect Value with Peek

Anonymous — Wed, 01 Mar 2017 11:17:59 GMT

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;

Re: Incorrect Value with Peek

sunny_talwar — Wed, 01 Mar 2017 11:29:03 GMT

What is your expected output here?

Re: Incorrect Value with Peek

Anonymous — Wed, 01 Mar 2017 11:37:11 GMT

rest of dates

Re: Incorrect Value with Peek

stabben23 — Wed, 01 Mar 2017 11:42:46 GMT

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,

Re: Incorrect Value with Peek

sunny_talwar — Wed, 01 Mar 2017 11:43:00 GMT

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

Re: Incorrect Value with Peek

Anonymous — Wed, 01 Mar 2017 12:21:14 GMT

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

Re: Incorrect Value with Peek

stabben23 — Wed, 01 Mar 2017 12:31:47 GMT

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

Peek() or Previous() ?

Re: Incorrect Value with Peek

swuehl — Wed, 01 Mar 2017 12:40:51 GMT

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

Re: Incorrect Value with Peek

stabben23 — Wed, 01 Mar 2017 13:11:43 GMT

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,