topic IntervalMatch question in QlikView

IntervalMatch question

raynac — Wed, 07 Sep 2011 16:13:17 GMT

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 — Wed, 07 Sep 2011 16:33:31 GMT

Yes, this is quite possible:

You define your rates with the "from-to" ranges.
Then, you define your main "Date" field in the Master Calendar.
Then, you link the two tables using Intervalmatch.
Now, each "Date" is associated to one or more Rates, based on the corresponding date ranges.
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.
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

raynac — Wed, 07 Sep 2011 19:22:16 GMT

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

IntervalMatch question

Oleg_Troyansky — Wed, 07 Sep 2011 20:06:16 GMT

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

IntervalMatch question

raynac — Wed, 07 Sep 2011 20:13:02 GMT

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!