Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Yes, this is quite possible:
cheers,
Oleg
Yes, this is quite possible:
cheers,
Oleg
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
In your INTERVALMATCH, you are linking to the TempDate, perhaps you should be linking to the field ValidDate instead...
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!