2 Replies Latest reply: Mar 25, 2015 7:18 AM by Matthew Bryant RSS

    Nonequijoin in Load

    Matthew Bryant

      I currently have two tables in my QVW: one is a list of dates, and the other is a range of dates with period numbers.

       

      Date
      01/02/2011
      02/02/2011
      03/02/2011
      04/02/2011

       

      Date FromDate ToPeriod
      01/02/201101/02/20111
      02/02/201103/02/20112
      04/02/201104/02/20113

       

      (Dates are in DD/MM/YYYY format)

       

      In SQL I could use a nonequijoin to link these table, but I don't believe this is possible in Qlikview. How can I obtain the following table?

       

      DatePeriod
      01/02/20111
      02/02/20112
      03/02/20112
      04/02/20113
        • Re: Nonequijoin in Load
          Aaron Morgan

          Hi Matthew,

           

          This is done using an IntervalMatch load (you can read more about it here: http://community.qlik.com/docs/DOC-4310)

           

          The premise is to match up your dates to your 'intervals' as your date to/date from:

           

          Intervals:
          IntervalMatch (Date)
          Load
          [Date From],
          [Date To]
          Resident Periods;

           

          You can then create the table you're after by simply creating a tablebox with Date and Period in. As a warning, it will create a synthetic join in your model, but this is normal and expected.

           

          Aaron

            • Re: Nonequijoin in Load
              Matthew Bryant

              Thanks Aaron,

               

              This seems to do the job pretty well, but I think I've stumbled upon a solution that's better for me inLoops in the Script. If I load the table of periods (in my case this is from an Oracle database) I can then loop through with the following script:

               

              Dates:

              Load

                    Period,

                    Date( DateFrom + IterNo() - 1 ) as Date

               

              Resident Periods

              While IterNo() <= DateTo - DateFrom + 1;

               

              Your solution may be better in other situations so I'll hold on to that. Thanks!