Can I use an IF statement within IntervalMatch?

[#1]

I want to use IntervalMatch to link absence instances to an employee appointment, but need to know how to introduce an if statement to the syntax in relation to one of my date fields.

For example: An employee initially joined the Company as a Junior Administrator on September 7th 2009 (appointment A) and was promoted to a Senior Administrator on February 22nd 2010 (appointment B), giving a date range of 07/09/2009 to 21/02/2010 for appointment A and 22/02/2010 to ? for appointment B (i.e. the employee is still active and so no end date for the range exists). The employee was absent from work on March 1st 2010 - I want to use IntervalMatch to match the absence start date with the relevant appointment and so would plan to use:

IntervalMatch:

inner join (Absence) intervalmatch ([ABSENCE START], Absence_Employee) load [Appointment Start Date], [Appointment End Date], Absence_Employee resident Appointments;

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. I'd like to introduce an IF statement to replace any null [Appointment End Date] values with today's date, e.g.:

inner join (Absence) intervalmatch ([ABSENCE START], Absence_Employee) load [Appointment Start Date], (if([Appointment End Date]<1,varToday,[Appointment End Date]), Absence_Employee resident Appointments;

This syntax doesn't work though - any ideas how I can include the necessary IF statement within my script in relation to IntervalMatch? Any guidance appreciated [:)]

[johnw]

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.

[#3]

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...

[johnw · Accepted solution]

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.

[#5]

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