3 Replies Latest reply: Jan 12, 2018 3:33 PM by Juraj Misina RSS


    Justin Dallas

      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

        • Re: Gaps-And-Islands
          Juraj Misina

          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.


            • Re: Gaps-And-Islands
              Justin Dallas

              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.

                • Re: Gaps-And-Islands
                  Juraj Misina



                  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.