2 Replies Latest reply: Dec 31, 2012 3:54 AM by azatti00 RSS

    Join and Interval match

      Hi all,

      Sorry for the dummy qustion but I'm a very newbie.

      I have to join this two table:

       

      SESSION_TABLE

       

      Session Time
      SeName
      User
      ID
      2012-12-26 08.00Action0Name1ID_1

      2012-12-26 15.00

      Action2

      Name2ID_2

      2012-12-27 08.00

      Action1

      Name3ID_3

      2012-12-27 19.00

      Action5Name1ID_4

       

      TASK_TABLE:

       

       

      Time Start
      Time Stop
      Task ID
      Task User
      2012-12-26 07:502012-12-26 08:35TASK01Name1
      2012-12-26 14:302012-12-26 16:40TASK02Name2
      2012-12-27 07.002012-12-26 07:50TASK03Name3

       

      And I would like to join the table and I have to create the following two table:

       

      TBJOIN1 (list all the Session with the related Task.)

       

      The join condition is: The Session Time is included in the range Task Time Start and Task Time Stop AND Session User Name is the Same Task User:

       

       

      TASK.TIMESTART
      TASK.TIMESTOP
      TASK.TASKID
      TASK.USER
      SESSION.TIME
      SESSION.USER
      SESSION.SENAME
      SESSION.ID
      2012-12-26 07:502012-12-26 08:35TASK01Name12012-12-26 08.00Name1Action0ID_1
      2012-12-26 14:302012-12-26 16:40TASK02Name22012-12-26 15.00Name2Action2ID_2
      2012-12-27 07.002012-12-26 07:50TASK03Name3----
      ----2012-12-27 08.00Name3Action1ID_3
      ----2012-12-27 19.00Name1Action5ID_4

       

      I tried with the Extended interval match...but it doesn't work. Any Idea?

        • Re: Join and Interval match
          Sander Janssen

          Hi, you can do the following:

           

          -----------------------------------------------------------------------

          TASK_TABLE:
          LOAD * INLINE [
          Time Start, Time Stop, Task ID, Task User
          2012-12-26 07:50, 2012-12-26 08:35, TASK01, Name1
          2012-12-26 14:30, 2012-12-26 16:40, TASK02, Name2
          2012-12-27 07:00, 2012-12-26 07:50, TASK03, Name3
          ];

           

          SESSION_TABLE:
          LOAD * INLINE [
          Session Time, SeName, User, ID,
          2012-12-26 08:00, Action0, Name1, ID_1
          2012-12-26 15:00, Action2, Name2, ID_2
          2012-12-27 08:00, Action1, Name3, ID_3
          2012-12-27 19:00, Action5, Name1, ID_4
          ];

           

          LEFT JOIN
          IntervalMatch([Session Time])
          LOAD
          [Time Start],
          [Time Stop]
          RESIDENT TASK_TABLE;

           

          JOIN
          LOAD *
          RESIDENT TASK_TABLE;

           

          DROP Table TASK_TABLE;

          -------------------------------------------------------------------------------

           

          The second join is an outer join (default QlikView). Also see attached.

          Regards, Sander