Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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.

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

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;

View solution in original post

12 Replies
Highlighted
Specialist III
Specialist III

hello

ass this to your code

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

resident Enrollment;

Highlighted
Master II
Master II

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;

Capture.PNG

Capture1.PNG

Highlighted
Contributor II
Contributor II

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.

Highlighted
Master II
Master II

What is your expected output?

Highlighted
Contributor II
Contributor II

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

];

Highlighted
Specialist III
Specialist III

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;

View solution in original post

Highlighted
Master II
Master II

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;

Capture.PNG

Highlighted
Contributor II
Contributor II

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!

Highlighted
Contributor II
Contributor II

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