How to associate a record to one of many matching records?

[#1]

Hi,

I have a situation where I'm trying to select one record out of many based on the date of an appointment.

I have for example the following two tables (in csv files) involving students having appointments. The first contains data about the student, the second about the appointment:

STUDENT:

Student ID	ID/Instance	Course	Start date	End date
ABC	ABC/1	BSc Mathematics	1/8/2000	31/7/2003
ABC	ABC/2	MSc Mathematics	1/8/2003	31/7/2004
ABC	ABC/3	PhD Mathematics	1/8/2004	31/7/2007

APPOINTMENT:

Appointment ID	Appointment Date	Student ID
1	1/1/2002	ABC
2	1/1/2005	ABC

What I need to do is find a way to connect the Appointment ID to the most appropriate ID/Instance based on the date of the appointment. I.e. Appointment 1 took place while Student ABC was studying the BSC Mathematics, while Appointment 2 took place while ABC was studying the PhD Mathematics.

So I want a table like this:

APPOINTMENT:

Appointment ID	Appointment Date	Student ID	Student ID/Instance
1	1/1/2002	ABC	ABC/1
2	1/1/2005	ABC	ABC/3

This would enable me to analyse appointment data based on the circumstances of the student at the time of the appointment. A simple association based on Student ID however gives me something like this:

Appointment ID	Appointment Date	Student ID	Student ID/Instance
1	1/1/2002	ABC	ABC/1
1	1/1/2002	ABC	ABC/2
1	1/1/2002	ABC	ABC/3
2	1/1/2005	ABC	ABC/1
2	1/1/2005	ABC	ABC/2
2	1/1/2005	ABC	ABC/3

How would I do this in QlikView?

Thanks.

[#2]

Date doesn't seem to be the issue, I've tried adding the date() and date#() functions to all date fields just to be sure (and set number format to 'Date' in Settings>Document Properties>Number), but no change.to what I'm after

I get something close to what I'm looking for by adding a left join before the interval match:

Student:

LOAD [Student ID],

     [ID/Instance],

     [Course],

     [Start date],

     [End date]

FROM [https://community.qlik.com/thread/205698]

(html, codepage is 1252, embedded labels, table is @1);

Appointment:

LOAD [Student ID],

     [Appointment ID],

     [Appointment Date]

FROM [https://community.qlik.com/thread/205698]

(html, codepage is 1252, embedded labels, table is @2);

left join IntervalMatch([Appointment Date],[Student ID])

LOAD [Start date],

     [End date],

     [Student ID]

Resident Student;

Which gives:

Appointment ID	Appointment Date	Student ID	ID/Instance
1	1/1/2002	ABC	ABC/1
2	1/1/2005	ABC	ABC/3
-	-	ABC	ABC/2

[alexpanjhc]

Oleg,

I think if you changed it to

SET DateFormat='D/M/YYYY';

you will load the end date.

However, i am still getting the same result as Lee.

[Oleg_Troyansky]

The INTERVALMATCH logic doesn't seem to work properly in this case, and I have no idea why. The only way I got the desired result is using the INNER JOIN (table1) with the INTERVALMATCH

[#5]

Thanks both, at this point I think I'll either join outside of QlikView and import or else move to an SQL database,

Lee