3 Replies Latest reply: Dec 5, 2017 12:52 PM by Sunny Talwar RSS

    Match events with intervals of time

    Fernando Almagro

      Hello to everyone.

      I'm quite new to Qlik Sense and I need some help to match discrete events with an interval of time.

      I have these two tables:


      Sales:

      Floor, DateTime, Sales, NuLine
      1, '23/11/2017 00:01', '105,41', 1
      1, '23/11/2017 07:28', '120,45', 2
      1, '23/11/2017 09:10', '138,54', 3
      2, '23/11/2017 19:28', '122,68', 4
      3, '23/11/2017 21:26', '17,52', 5
      2, '23/11/2017 23:58', '157,83', 6
      2, '24/11/2017 00:06', '149,16', 7
      3, '24/11/2017 00:47', '180,82', 8
      3, '24/11/2017 01:34', '86,47', 9
      1, '24/11/2017 01:54', '101,94', 10

       

       

      Managers:
      FloorM, Manager, StartTime, EndTime, NoLine
      1, manolo, '23/11/2017 08:00', '23/11/2017 15:59', 1
      1, sara, '23/11/2017 16:00', '23/11/2017 23:59', 2
      2, pedro, '23/11/2017 00:00', '23/11/2017 07:59', 3
      3, jose, '24/11/2017 08:00', '24/11/2017 15:59', 4
      3, sara, '24/11/2017 16:00', '24/11/2017 23:59', 5

       

       

      I need to know how much money is made during the shifts of every floor manager.

      I've tried to use IntervalMatch but I haven't found an example that depends on some other field like Floor.

      I've discarded using a 'minutes' table to match every Sale with a Manager because it was too much millions rows of data per month.

      Finally, I have also tried to make this loop but it doesn't work as the "peek()" functions on the "let pasa = if(...)' always have 'null' value:

       

       

      for each nul in fieldValueList('NuLine')
          let para = 0;
          let i = 0;
          do while para = 0
                let i = i + 1;
                let para =
                    if(peek('Floor', $(nul), Sales) = peek('FloorM', $(i) - 1, Managers),
                        if(peek('DateTime', $(nul), Sales) >= peek('StartTime', $(i) - 1, Managers)
                        and peek('DateTime', $(nul), Sales) <= peek('StartTime', $(i), Managers), '1', '0')
                    , '0');
          loop
          Matched:
          Load
                '$(nul)' as NuLinea,
                '$(i)' as NoLinea;
      next nul;

       

       

      Could anyone help me with this?

        • Re: Match events with intervals of time
          Sunny Talwar

          Have you checked the Extended Interval Match?

          IntervalMatch (Extended Syntax) ‒ QlikView

           

          Somehting like this

          Sales:

          LOAD * INLINE [

              Floor, DateTime, Sales, NuLine

              1, 23/11/2017 00:01, "105,41", 1

              1, 23/11/2017 07:28, "120,45", 2

              1, 23/11/2017 09:10, "138,54", 3

              2, 23/11/2017 19:28, "122,68", 4

              3, 23/11/2017 21:26, "17,52", 5

              2, 23/11/2017 23:58, "157,83", 6

              2, 24/11/2017 00:06, "149,16", 7

              3, 24/11/2017 00:47, "180,82", 8

              3, 24/11/2017 01:34, "86,47", 9

              1, 24/11/2017 01:54, "101,94", 10

          ];

           

          Managers:

          LOAD * INLINE [

              Floor, Manager, StartTime, EndTime, NoLine

              1, manolo, 23/11/2017 08:00, 23/11/2017 15:59, 1

              1, sara, 23/11/2017 16:00, 23/11/2017 23:59, 2

              2, pedro, 23/11/2017 00:00, 23/11/2017 07:59, 3

              3, jose, 24/11/2017 08:00, 24/11/2017 15:59, 4

              3, sara, 24/11/2017 16:00, 24/11/2017 23:59, 5

          ];

           

          Left Join (Sales)

          IntervalMatch(DateTime, Floor)

          LOAD StartTime,

          EndTime,

              Floor

          Resident Managers;

           

          Left Join (Sales)

          LOAD *

          Resident Managers;

           

          DROP Table Managers;