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.

[olivierrobin]

hello

ass this to your code

inner join intervalmatch(AbsenceDate,Pupil) load Start,End,Pupil

resident Enrollment;

[qlikviewwizard]

Hi,

Please try this Code.

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 AbsenceDate, Amount 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_Table:

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

Join (Absence) LOAD distinct * RESIDENT Enrollment;

DROP TABLE Enrollment;

[sir_ogrim]

Hi and thank you for your response!

This code unfortunately seems to mix up the data. In the result Pupil Pup01 gets the AbsenceDate and Amount that belongs to Pup02 in the Absence table.

[qlikviewwizard]

What is your expected output?

[sir_ogrim]

The expected output would look like this:

Expected_Output:

LOAD Pupil, AbsenceDate, Amount, School inline

[Pupil, AbsenceDate, Amount, School

Pup01, 2018-07-01, 30, null

Pup01, 2018-02-01, 45, School02

Pup02, 2017-04-15, 15, School02

Pup02, 2018-04-01, 15, School03

];

[olivierrobin · Accepted solution]

finally, here is the code

inner join intervalmatch(AbsenceDate,Pupil) load Start,End,Pupil

resident Enrollment;

final_table:

NoConcatenate

load Pupil,AbsenceDate,Amount,Start,End

resident Absence;

left join(final_table)

load Pupil,Start,End,School

resident Enrollment;

drop table Enrollment;

drop table Absence;

[qlikviewwizard]

Hi,

Please check now.

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

];

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

];

Final_Table:

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

Join (Absence) LOAD distinct * RESIDENT Enrollment;

DROP TABLE Enrollment;

[sir_ogrim]

There it is indeed! Thank you very much! This solved the the problem and produced a finished table that will be very quick to access. Thank you for the help!

[sir_ogrim]

Thank you! This solution also includes the row where there is no Scool match. That is very nice. Thank you for your help!