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

    Gaps-And-Islands

    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.

       

      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


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

          Juraj

            • 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

                  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