Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
That's a cool solution Marco! I like it.
-Rob
Hi Rob,
It really is an honor to hear this from you.
thank you
regards
Marco