Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Folks,
I have a dataset, whereby I need to condense time ranges from a sea of datapoints. For example, let's say I have the following script.
WindowFunctions:
LOAD * Inline
[
TimeStamp
2017-01-11 11:19:32
2017-01-11 11:20:32
2017-01-11 11:21:32
2017-01-12 11:35:32
2017-01-12 11:36:36
2017-01-12 11:37:28
]
;
My goal is to take all data points that occur within 3 minutes of one another, and combine them into a range. I would expect to get an endresult that looks like this:
SomeTable
StartTime EndTime
2017-01-11 11:19:32 2017-01-11 11:21:32
2017-01-12 11:35:32 2017-01-12 11:37:28
I have a similar question where I've asked about a similar problem, but it concerned Packing Intervals where I had nice clean dates which would line up. With this problem, I'm looking at it from a minute sized granularity.
Any help is greatly appreciated.
2017-01-11 11:19:32
Hi Justin,
I think you could generate a table with intervals you'd like to achieve (perhaps autogenerate them based on your start time) and then join these intervals onto original table using IntervalMatch
Hope this helps.
Juraj
Thanks for your help on this lonely thread. My only issue is that I don't know the intervals, and that I have to figure them out from the data.
Justin,
lets assume there's a table called Table1 with a field called Timestamp which contains your timestamp. Then you can:
Min_Timestamp:
LOAD
Min(Timestamp) as MinTime,
Max(Timestamp) as MaxTime
resident Table1;
Intervals:
LOAD
FieldValue('MinTime', 1)+((1/24/60)*3*(IterNo()-1) as StartTime,
FieldValue('MinTime', 1)+((1/24/60)*3*(IterNo())-(1/24/60/60) as EndTime
Autogenerate 1 While (FieldValue('MinTime', 1)+((1/24/60)*3*(IterNo()))<FieldValue('MaxTime', 1);
So if your first time is 17:00:00, then your intevals would be
17:00:00 - 17:02:59
17:03:00 - 17:05:59
...
And than you can proceed with the intervalmatch.
I'm pretty sure there are typos in my script, I did not test it, so you will need to fine tune it.
Juraj