Gaps-And-Islands - Qlik Community

JustinDallas · ‎2018-01-12

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.

Packing Intervals in Qlik

Any help is greatly appreciated.

2017-01-11 11:19:32

juraj_misina · ‎2018-01-12

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

JustinDallas · ‎2018-01-12

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.

juraj_misina · ‎2018-01-12

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