4 Replies Latest reply: Sep 7, 2011 4:13 PM by Rayna Curtis RSS

    IntervalMatch question

    Rayna Curtis

      I have read many posts on this subject and have not seen one that addresses what I am trying to do, so I am hoping someone can tell me if Qlikview is even capable of doing this.

       

      I have a table that contains rate_type, start_date and end_date.  The rate_type can exist several times with multiple sets of dates.

       

      I am looking to build a view wherein the user can choose a range of dates (from a listbox) and get a list of all rate types that were valid on any one of those dates.

       

      I looked at building a master calendar, using the to and from dates, and then using IntervalMatch() for the comparison.  But all indications I see indicate that if I was able to do this at all, it would be for a single date only.

       

      Am I missing something?  Any help you can give me would be much appreciated.

        • IntervalMatch question
          Oleg Troyansky

          Yes, this is quite possible:

           

          1. You define your rates with the "from-to" ranges.
          2. Then, you define your main "Date" field in the Master Calendar.
          3. Then, you link the two tables using Intervalmatch.
          4. Now, each "Date" is associated to one or more Rates, based on the corresponding date ranges.
          5. Now, you need to provide a selection capability for your users. You can either give them a List Box for the "Date" field, or a pair of variables "From Date" - "To Date" with a couple of actions to cause selection in the Date field based on the desired rabge. List box is certainly easier to implement.
          6. Every time the user selects a single date or multiple dates from the List Box (or a date range via variables), the associated Rates will be available for charts and other objects.

           

          cheers,

           

          Oleg

            • IntervalMatch question
              Rayna Curtis

              Hi Oleg - thank you so much for your response.  I appreciate it a great deal!

               

              I have added the code that I think should work - but it does not seem to have any effect.  It loads fine and the table viewer looks like it all connects, but when I click on the listbox with the new date field, it selects the date(s) but then has no efffect on the reports.  I only learned this concept on course last week, so perhaps I am not quite getting the syntax.

               

              Here is the code (fdratetype is the table that holds all the rate types):

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

              LET varMinDate = Num(Peek('from_date', 0, 'fdratetype'));

               

              LET varMaxDate = Num(Peek('to_date', -1, 'fdratetype'));

               

              LET varToday = num(today());

               

              //*************** Temporary Calendar ***************

              DateField:

              LOAD

                  $(varMinDate) + rowno() - 1 AS Num,

                  date($(varMinDate) + rowno() - 1) AS TempDate

              AUTOGENERATE

                  $(varMaxDate) - $(varMinDate) + 1;

                 

              //*************** Master Calendar ***************

              MasterCalendar:

              LOAD   

                  TempDate AS ValidDate,

                  Week(TempDate) AS Week,

                  Year(TempDate) AS Year,

                  Month(TempDate) AS Month,

                  Day(TempDate) AS Day,

                  Weekday(TempDate) AS WeekDay,

                  'Q' & ceil(month(TempDate) / 3) AS Quarter,

                  Date(monthstart(TempDate), 'MMM-YYYY') AS

                  MonthYear,

                  Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

                  inyeartodate(TempDate, $(varToday), 0) * -1 AS

                  CurYTDFlag,

                  inyeartodate(TempDate, $(varToday), -1) * -1 AS

                  LastYTDFlag

              RESIDENT DateField

              ORDER BY TempDate ASC;

               

              INTERVALMATCH(TempDate)

              LOAD DISTINCT

                  from_date,

                  to_date

              RESIDENT

                  fdratetype;

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

               

              Do you see anyplace I am going wrong?

               

              Thank you!

              Rayna