Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
raynac
Partner - Creator
Partner - Creator

IntervalMatch question

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.

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

4 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

raynac
Partner - Creator
Partner - Creator
Author

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

In your INTERVALMATCH, you are linking to the TempDate, perhaps you should be linking to the field ValidDate instead...

raynac
Partner - Creator
Partner - Creator
Author

You are the best!  Thank you so much.

For the record, I was sure I tried that earlier today and that it errored out - that was why I changed it to the TempDate.  But I am glad I made the attempt a second time before posting that response, because this time it worked perfectly.  Perhaps the first time I messed up the upper/lower casing of my variable.

Again, thank you!