I am working on a scholarship report that I need to add a select feature based on the student course of study
In the design, we will use the session filter (2000/2001) to select student who are valid to receive scholarship payment each session.
For example, using the table below
In Session 2000/2001 John and Flowers are eligible for payment
In Session 2002/2003 All Candidate are eligible for payment
In Session 2007/2008 Only Dede is eligible for payment
|S/N||Name||Sex||School||Session||Course of Study||Duration|
|1||John Brown||Male||Jim High School||2000/2001||Fine Art||6|
|2||Eze Francis||Male||St. Pauli University||2001/2002||Engineering||5|
|3||Dede Chi||Female||St. Pauli University||2002/2003||Medicine||6|
|4||Flowers Kepp||Female||St. Pauli University||2000/2001||Geology||4|
Thank you for your response.
I have done a simple app.
There are two tables... one that holds the student's record (I shared that one earlier) and a eligibility session table.
Let say Dede Chi is studying Medicine and gain admission into the University on 2002/2003 academic session. Her course of study is medicine and that will take her six years to complete. Her scholarship duration is six years. meaning she will get paid in the following session: 2002/2003, 2003/2004,2004/2005,2005/2006, 2006/2007 and 2007/2008.
My proposed design is to use the eligibility session table are a tracker to see who will be paid in the selected session on this eligibility table.
So If I select 2007/2008.. Dede Chi name should appear and if I select 2010/2011.. No body name because all four students will be done with their academic exercise.
Hope this helps
Ok, I think I got it.
I don't know of this is possible with Set Analysis or not. I would recommend you the "As Of" idea to solve this:
First I need a YearStart based on the list of students:
Left(Session,4) as YearStart;
"Course of Study",
(ooxml, embedded labels, table is Students);
Then, I need a table of Years to be used with the selections:
// Derive relevant years (beginning and end)
Load Min(YearStart) as MinYear,
Max(YearStart+Duration) as MaxYear
Let vMinYear = Peek('MinYear', 0, 'Temp');
Let vMaxYear = Peek('MaxYear', 0, 'Temp');
Drop Table Temp;
// Create Table with Years
($(vMinYear)+IterNo()-1) & '/' & ($(vMinYear)+IterNo()) as SessionYear
AutoGenerate 1 While IterNo() <= $(vMaxYear)-$(vMinYear);
Then I would create a Control table that links all the years to match each student:
// Create a control table
Let vNumberOfStudents = NoOfRows('Students');
For vCounter = 1 to $(vNumberOfStudents)
Let vStudentNumber = Peek('S/N' ,vCounter-1,'Students');
Let vYearStart = Peek('YearStart',vCounter-1,'Students');
Let vDuration = Peek('Duration' ,vCounter-1,'Students');
$(vStudentNumber) as "S/N",
($(vYearStart)+IterNo()-1) & '/' & ($(vYearStart)+IterNo()) as SessionYear
AutoGenerate 1 While IterNo() <= $(vDuration);
With that you would create this data model:
Which will easily let you build a filter pane (Qlik Sense jargon) with the Years and a straight table with the Students
And when selection 2007/2008 you'll see this:
Please try and keep me posted,
num(subfield(Session,'/',1)) as begin,
Rangesum(num(subfield(Session,'/',1)),Duration) as end,
[Course of Study],
[mock scholarship data.xlsx] (ooxml, embedded labels, table is Sheet1);
for i= 2000 to 2010
load $(i) as session AutoGenerate 1;
IntervalMatch (session) LOAD begin, end Resident DATA;
Now you have other options; I really liked Antonio's and Robin's idea to use IntervalMatch... Another function for me to learn!!!!!
When applicable please mark the appropriate replies as Helpful (2) and Correct (1).