how to deal with two Interval Matches

dijkstram — Wed, 20 Mar 2013 22:04:11 GMT

Please some help,

We have two time periods (intervals) with a begindate and and enddate; the study enrollment period of a student and his participation during a certain period in a lesson group. These intervals can be different but have some time periods in common.

On a reference date we want to count all the students which participate in a lesson group and have a study enrollment on the same reference date.

For example:

Student Group participation Enrollment

ID Begindate Endate Begindate Enddate

---------------------------------------------------------------------------------------------------------------

2500 8-1-2013 12-1-2013 8-1-2012 8-1-2015

2501 10-1-2013 12-1-2013 8-1-2013 8-1-2015

Reference date # Students

------------------------------------------------

9-1-2013 1

11-1-2013 2

How to handle this in the script with two different Intervalmatches.

Table Calendar ( Date, Month, Quarter, Year)

Table Group with columns ( StudentID, Groupname, begindate, enddate)

Table Enrollments with columns (StudentID, trainingname, begindate, enddate,..,..,..)

I appreciate all your suggestions

Thanks in advance,

Re: how to deal with two Interval Matches

hic — Thu, 21 Mar 2013 07:49:41 GMT

You should have a data model like the one in the picture below. It will take some scripting, but it is doable.

The bridge tables can for instance be created using a while loop:

[Student & Date & Group]:

Load

StudentID & '|' & Date(begindate + IterNo() - 1) as 'Student & Date',

StudentID & '|' & Num(begindate) & '|' & Num(enddate) as 'Student & GroupInterval'

From Group

While begindate + IterNo() - 1 <= enddate;

HIC

Re: how to deal with two Interval Matches

dijkstram — Thu, 21 Mar 2013 21:26:22 GMT

Thanks Henric,

It solved my problem, very helpfull !!

topic Re: how to deal with two Interval Matches in QlikView

how to deal with two Interval Matches

Re: how to deal with two Interval Matches

Re: how to deal with two Interval Matches