Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 
Specialist II
Specialist II


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

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.


Specialist II
Specialist II

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.



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.