Question regarding peek/previous

[mazacini]

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:

    >

[swuehl · Accepted solution]

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;

[mazacini]

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