6 Replies Latest reply: Jan 14, 2015 6:06 PM by Marco Wedel

# 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 :

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 ?

Have a good day.

Laura

• ###### Re: Error with peek/previous

Hi Laura,

Try something like

Table1:

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

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.

```T1:
XXX, timestamp
A, 42005.000045
A, 42007.000004
A, 42008.022222
B, 42004.999999
B, 42008.000343
];

T2:
*,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

Modifying Anand's example a bit:

T1:

XXX, timestamp

A, 42005.000045

A, 42007.000004

A, 42008.022222

B, 42004.999999

B, 42008.000343

];

T2:

*

WHERE Interval > 0

;

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://robwunderlich.com

• ###### Re: Error with peek/previous

Hi,

one possible solution could be also:

```tab1:
Timestamp(timestamp) as EndTimestamp
INLINE [
XXX, timestamp
A, 42005.000045
A, 42007.000004
A, 42008.022222
B, 42004.999999
B, 42008.000343
];

Right Join
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

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