All dates on and between two other dates

[DanielP065]

Hello,

I have two tables, TERMS and STUDENTS,

In the Terms Table, I have the Terms.Name from the past several years in the format 2018-2019, 2019-2020... I also have the corrisponding Terms.StartDate 8/12/2019 and Terms.EndDate 5/29/2020 of each year.

In the Student Table I've got the Students.EntryDate that can happen anytime during the year.

What I'm need help with is how to write a QLIK Sense equivalent to the SQL:

SELECT DISTINCT
students.lastfirst,
students.grade_level,
students.enroll_status,
students.student_number,
students.entrydate,
students.exitdate,
students.schoolid,
terms.name,
terms.id,
terms.yearid
FROM
ps.students
Left Outer Join terms ON students.entrydate BETWEEN terms.firstday AND terms.lastday
WHERE
terms.name = '2019-2020'

What I'm trying to do is to create a filter on the Terms.Name in my App, that will select only students that were enrolled between any term that is selected. Where I'm getting stuck is figuring out how to do the Left Outer Join on the Terms table?

Thank you!
Daniel

[Vegar]

I believe you could be helped by using intervalmatch. If you're not familiar with intervalmatch then take a look at the will help page https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

[jonathandienst · Accepted solution]

Something like this:

Terms:
SQL SELECT 
	name,
	id,
	yearid,
	firstday,
	lastday
FROM terms;

Students:
SQL SELECT DISTINCT
	lastfirst,
	grade_level,
	enroll_status,
	student_number,
	entrydate,
	exitdate,
	schoolid,
	entrydate
FROM ps.students;

Left Join (Students)
IntervalMatch(entrydate)
LOAD firstday, lastday
Resident Terms;

//Optional, reduces the number of tables and removes the syn key

Left Join (Students)
LOAD * 
Resident Terms;

DROP Table Terms;
DROP Fields firstday, lastday;

The optional portion is not required, but tidies up the data model and removes the syn key. This type of syn key is OK in a data model, so removing it is a choice of style.