Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable


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.


LOAD * Inline



    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:


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

3 Replies
Not applicable

Re: Gaps-And-Islands

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.


Not applicable

Re: Gaps-And-Islands

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.

Not applicable

Re: Gaps-And-Islands


lets assume there's a table called Table1 with a field called Timestamp which contains your timestamp. Then you can:



Min(Timestamp) as MinTime,

Max(Timestamp) as MaxTime

resident Table1;



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.