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.

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

Gaps-And-Islands

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


3 Replies
Luminary
Luminary

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

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.

Luminary
Luminary

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