topic Question regarding peek/previous in QlikView

Question regarding peek/previous

mazacini — Fri, 13 Dec 2013 11:37:24 GMT

Hi. See the attached image/spreadhseet. I want to load the data shown on the left to create a table shown on the right.

New fields highlighted.

Duration is calculated by taking start time from NEXT trip from start time from CURRENT trip, except for last item each day, where start time and end time are the same, and duration is 0 (shown in Red).

I know Qlikview does not do NEXT, so my plan was to load in descending order, then use PEEK/PREVIOUS.

But I need help.

Here is my script:

Re: Question regarding peek/previous

swuehl — Fri, 13 Dec 2013 11:53:51 GMT

It would make things easier if you post your script code as text, not as image.

I would create the DropStart Timestamp from Date + ATA, and I would also remove single quotes from the first previous() function:

Duration:

LOAD Date(Date,'DD/MM/YYYY') &Trip as DateTripCode,

[Crew 1],

[Crew 2],

Timestamp(Date + ATA) as DropStart,

[Location Ref]

FROM

[Trip Table_2.xlsx]

(ooxml, embedded labels, table is Sheet1);

Duration2:

LOAD *,

if(DateTripCode = previous(DateTripCode), previous(DropStart),DropStart) as DropEnd

Resident Duration

Order by DateTripCode, DropStart desc;

drop table Duration;

Re: Re: Question regarding peek/previous

mazacini — Fri, 13 Dec 2013 12:22:09 GMT

Many thanks for your response - worked perfectly.

As regards posting my script as image - I don't seem to be able to paste the text into my message.

I don't know why. In fact, when I try and paste text, nothing appears, and it seems to disable the message area!

Anyway, thanks for your help