Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

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
juraj_misina
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.

Juraj

JustinDallas
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.

juraj_misina
Not applicable

Re: Gaps-And-Islands

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