Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Error with peek/previous

Hi everyone,

I need to use peek or previous function so I can calculate the difference of dates between 2 rows.

The table I load looks like this :

XXX   timestamp

A        42005,000045

A        42007,000004

A        42008,022222

B        42004,999999

B        42008,000343

And I would like to obtain :

XXX      interval

A           (42007,000004-42005,000045)   --> I would like the result in minutes

A           (42008,022222-42007,000004)

B           (42008,000343-42004,999999)

I also would like to get rid of the first line of each 'XXX' because I cannot calculate the interval.

I was thinking of something like

if(XXX = Peek(XXX), interval(timestamp-Peek(timestamp), 'm'))

I first tried :

LOAD  XXX,

            timestamp,

            Peek(timestamp) as test

FROM ....

order by XXX

But I get the error "Scories after the instruction"... I don't know how to deal with that ? Is that because this field doesn't exist for the first row of each "XXX" ?

What can I do ?

Thanks for your help

Have a good day.

Laura

1 Solution

Accepted Solutions

Re: Error with peek/previous

Hi,

Try this ways check for previous value and then add into the resident table and then in next resident subtract it with the timestamp field.

Check load script

T1:

LOAD * INLINE [

    XXX, timestamp

    A, 42005.000045

    A, 42007.000004

    A, 42008.022222

    B, 42004.999999

    B, 42008.000343

];

T2:

LOAD

*,if(XXX = Previous(XXX), Peek(timestamp)) as [timestamp Previous]

Resident T1 Order By XXX, timestamp;

DROP Table T1;

Final:

LOAD *, Interval(timestamp - [timestamp Previous],'mm') as Diff

Resident T2 Where IsNull([timestamp Previous]) <> -1;

DROP Table T2;

Regards

Anand

6 Replies
marcus_malinow
Valued Contributor III

Re: Error with peek/previous

Hi Laura,

Try something like

Table1:

LOAD

XXX,

timestamp,

if(NOT IsNull(Peek('XXX', -1),

    if(Peek('XXX', -1) = XXX,

        peek('timestamp', -1))) as previoustimestamp

FROM

....

ORDER BY XXX, timestamp

Table2:

NOCONCATENATE

LOAD

XXX,

timestamp,

previoustimestamp,

timestamp - previoustimestamp as timestampdiff

RESIDENT Table1

WHERE NOT IsNull(previoustimestamp);

DROP TABLE Table1;

Marcus

Re: Error with peek/previous

Hi,

Try this ways check for previous value and then add into the resident table and then in next resident subtract it with the timestamp field.

Check load script

T1:

LOAD * INLINE [

    XXX, timestamp

    A, 42005.000045

    A, 42007.000004

    A, 42008.022222

    B, 42004.999999

    B, 42008.000343

];

T2:

LOAD

*,if(XXX = Previous(XXX), Peek(timestamp)) as [timestamp Previous]

Resident T1 Order By XXX, timestamp;

DROP Table T1;

Final:

LOAD *, Interval(timestamp - [timestamp Previous],'mm') as Diff

Resident T2 Where IsNull([timestamp Previous]) <> -1;

DROP Table T2;

Regards

Anand

MVP & Luminary
MVP & Luminary

Re: Error with peek/previous

Modifying Anand's example a bit:

T1:

LOAD * INLINE [

    XXX, timestamp

    A, 42005.000045

    A, 42007.000004

    A, 42008.022222

    B, 42004.999999

    B, 42008.000343

];

T2:

LOAD

  *

WHERE Interval > 0

;

LOAD

  XXX,

  if(XXX = Previous(XXX), interval(timestamp - Previous(timestamp), 'm')) as Interval

Resident T1 Order By XXX, timestamp

;

I don't think peek() is required, just previous().

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Error with peek/previous

Hi,

one possible solution could be also:

QlikCommunity_Thread_148410_Pic1.JPG

tab1:

LOAD XXX,

    Timestamp(timestamp) as EndTimestamp

INLINE [

    XXX, timestamp

    A, 42005.000045

    A, 42007.000004

    A, 42008.022222

    B, 42004.999999

    B, 42008.000343

];

Right Join

LOAD *,

    Previous(EndTimestamp) as StartTimestamp,

    Interval(EndTimestamp-Previous(EndTimestamp),'mm:ss') as [Interval (mm:ss)]

Resident tab1

Where XXX=Previous(XXX)

Order By XXX, EndTimestamp;

hope this helps

regards

Marco

MVP & Luminary
MVP & Luminary

Re: Error with peek/previous

That's a cool solution Marco! I like it.

-Rob

Re: Error with peek/previous

Hi Rob,

It really is an honor to hear this from you.

thank you

regards

Marco