topic Re: Error with peek/previous in QlikView

Error with peek/previous

Wed, 14 Jan 2015 16:52:15 GMT

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

Re: Error with peek/previous

marcus_malinow — Wed, 14 Jan 2015 16:57:19 GMT

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

its_anandrjs — Wed, 14 Jan 2015 17:11:37 GMT

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

Re: Error with peek/previous

rwunderlich — Wed, 14 Jan 2015 18:29:46 GMT

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

MarcoWedel — Wed, 14 Jan 2015 21:42:21 GMT

Hi,

one possible solution could be also:

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

Re: Error with peek/previous

rwunderlich — Wed, 14 Jan 2015 23:00:08 GMT

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

-Rob

Re: Error with peek/previous

MarcoWedel — Wed, 14 Jan 2015 23:06:55 GMT

Hi Rob,

It really is an honor to hear this from you.

thank you

regards

Marco