NickyR wrote:Issue is that [Appointment End Date] will not be populated for active appointments and so the range being considered is incomplete and results limted accordingly.
From the intervalmatch help text:
"A lower or upper limit denoted by a NULL value is considered to be -infinity and infinity respectively. Other non-numeric limits render the interval to be disregarded."
The range isn't incomplete if the end date isn't specified. It is from the start date until the end of time. So either I'm misunderstanding you, or you're trying to solve a non-problem.
Thanks for the reply John. Perhaps if I expand on my issue more clearly for you, you'll be able to advise.
Using the IntervalMatch syntax in my earlier example (i.e. without the IF statement), all absence records associated with an employee who has null value for [Appointment End Date] fail to be retrieved. I'm currently working around this by using an IF statement within the Excel source data to create a substitute [Appointment End Date] column for use in relation to absences. This substitute column is being populated with the last day of the current month wherever the real [Appointment End Date] column is null.
I need to handover generation of the Excel source files to non-IT colleagues within the business so aim is to avoid them needing to modify the export from the source system wherever possible (source system is hosted so I'm unable to access the data directly).
Given the additional info, d'you have any ideas as to why I'm losing rows where [Appointment End Date] is null? I have already checked that data is being read as null values rather than ' ' so am at a slight loss here right now...
Hmmm, no idea why you're losing the rows. The only thing I was thinking of suggesting is making sure it was null instead of ' ', and you've already done that. It seems like it should work.
Still, we can probably hit it with a hammer until it behaves. How about moving the substitution of the end date to the QlikView side? Something like this during the load?
date(if([Appointment End Date],[Appointment End Date],daystart(monthend(today()))) as [Appointment End Date]
It's basically the same solution as you're currently using, just moving the maintenance of it to where you have control.
Thanks for coming back to me on this so quickly. You must be psychic as if there's no apparent answer for the loss of rows resulting from the null values in relation to IntervalMatch, my next question would've been how to script the substitute column within QlikView rather than within Excel! I'll try the suggestion when I get back into the office on Monday. Thanks again