Match data to an interval

[sir_ogrim]

Hi! I'm having a hard time getting my head around interval match in QlikView. I need to add data to one table based on an interval and a person id in another table. I could do this in seconds with SQL or one of our ETL tools, but I need to do it in QlikView and the data is stored in two different SQL databases.

Below is some simplified sample data. After that I will describe what I would like to achieve.

This data consists of two tables. Enrollment tells us which school each pupil was enrolled in and when. Absence tells us the amount of absence each pupil has had and on which date that occurred.

Enrollment:

LOAD * inline

[Pupil, Start, End, School

Pup01, 2015-08-01, 2017-07-31, School01

Pup01, 2017-08-01, 2018-06-30, School02

Pup02, 2015-08-01, 2017-09-31, School02

Pup02, 2017-10-01, 2018-06-30, School03

];

Absence:

LOAD * inline

[Pupil, AbsenceDate, Amount

Pup01, 2018-07-01, 30

Pup01, 2018-02-01, 45

Pup02, 2017-04-15, 15

Pup02, 2018-04-01, 15

];

In the end, I would like to have one table that looks like this:

+-------+-------------+--------+-----------------------------+

| Pupil | AbsenceDate | Amount | Pupil's school on this date |

+-------+-------------+--------+-----------------------------+

I obviously need to match the Pupil fields and interval match AbsenceDate to Start and End and return the School field. I just can't seem to figure out how...  

Any help would be greatly appreciated.

[Anonymous]

Hi,

this is working for me:

Enrollment:

LOAD * inline

[Pupil, Start, End, School

Pup01, 2015-08-01, 2017-07-31, School01

Pup01, 2017-08-01, 2018-06-30, School02

Pup02, 2015-08-01, 2017-09-31, School02

Pup02, 2017-10-01, 2018-06-30, School03

];

Absence:

LOAD * inline

[Pupil, AbsenceDate, Amount

Pup01, 2018-07-01, 30

Pup01, 2018-02-01, 45

Pup02, 2017-04-15, 15

Pup02, 2018-04-01, 15

];

Final:

    INTERVALMATCH(AbsenceDate, Pupil) LOAD Start, End, Pupil RESIDENT Enrollment;

    Left JOIN LOAD Pupil, Start, End, School Resident Enrollment;

    LEFT JOIN LOAD * Resident Absence;

    DROP FIELDS Start, End;

DROP TABLES Absence, Enrollment;

Hope it helps,

Regards,

Roger

[sir_ogrim]

Hi and thanks for your response! Not only does this work, but it is also a bit easier for me to understand as it is rather similar to the basic intervalmatch examples where you use only one key (like a date that is compared to a date interval). Thank you for this helpful addition!

[qlikviewwizard]

Hi sir_ogrim

I used JOIN instead of Left Join as I don't know initial requirement. Happy to part of solving this.