Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
sir_ogrim
Contributor II
Contributor II

Match data to an interval

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.

12 Replies
roger_mont
Contributor II
Contributor II

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
Contributor II
Contributor II
Author

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
Master II
Master II

Hi sir_ogrim

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