2 Replies Latest reply: Nov 10, 2016 4:23 AM by Grzegorz Mroz RSS

    How to make Join with BETWEEN clause in QlikView script

    Grzegorz Mroz

      Hello,

      I'm new to QlikView and looking for help, how to join two tables.

       

      I have two tables one with Activities:

      ActivityIDActivityTypeStartDateFinishDate
      1New2016-Nov-02 14:002016-Nov-04 15:00
      1In Progress2016-Nov-04 15:012016-Nov-05 07:01

       

      And one with TimeDim:

      Day IdDay ..
      20161102Wed

      20161103

      Thu
      20161104Fri
      20161105Sat

       

      I want to join them - to create fact table showing how much time was spend each day for ActivityType group by ActivityID:

      ActivityIdAcivityTypeDayIdTimeSpend [h]
      1New2016110210
      1New2016110324
      1New2016110415
      1In Progress201611049
      1In Progress201611057

       

       

      So I want to make such sql join but in QlikView:

       

      select ActivityId, ActivityType,DayId, calculateTimeSpend

      from Activities

      join TimeDim

      on ( TimeDim.Dayid between Activities.StartDate and Activities.EndDate )

      group by ActivityId, ActivityType, DayId