Error with peek/previous

[#1]

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

[marcus_malinow]

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

[its_anandrjs · Accepted solution]

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

[rwunderlich]

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

[MarcoWedel]

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

[rwunderlich]

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

-Rob

[MarcoWedel]

Hi Rob,

It really is an honor to hear this from you.

thank you

regards

Marco