topic Re: Match data to an interval in QlikView

Match data to an interval

sir_ogrim — Wed, 25 Nov 2020 16:16:04 GMT

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:

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

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

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.

Re: Match data to an interval

olivierrobin — Wed, 11 Jul 2018 07:31:47 GMT

hello

ass this to your code

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

resident Enrollment;

Re: Match data to an interval

qlikviewwizard — Wed, 11 Jul 2018 08:02:56 GMT

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;

Re: Match data to an interval

sir_ogrim — Wed, 11 Jul 2018 08:15:43 GMT

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.

Re: Match data to an interval

qlikviewwizard — Wed, 11 Jul 2018 08:17:17 GMT

What is your expected output?

Re: Match data to an interval

sir_ogrim — Wed, 11 Jul 2018 08:22:48 GMT

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

];

Re: Match data to an interval

olivierrobin — Wed, 11 Jul 2018 08:33:07 GMT

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;

Re: Match data to an interval

qlikviewwizard — Wed, 11 Jul 2018 08:35:12 GMT

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;

Re: Match data to an interval

sir_ogrim — Wed, 11 Jul 2018 08:40:27 GMT

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!

Re: Match data to an interval

sir_ogrim — Wed, 11 Jul 2018 08:48:46 GMT

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

Re: Match data to an interval

Anonymous — Wed, 11 Jul 2018 09:03:17 GMT

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

Re: Match data to an interval

sir_ogrim — Wed, 11 Jul 2018 09:11:52 GMT

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!

Re: Match data to an interval

qlikviewwizard — Wed, 11 Jul 2018 09:26:48 GMT

Hi sir_ogrim

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