Scholarship Table Analysis

[akpofureenughwu]

Hello everyone,

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

[luismadriz]

Hi Akpofure,

I think your requirement or issue is not clear, can you please elaborate?

Cheers,

L

[akpofureenughwu]

luismadriz‌

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.

Explanation

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

Regards 

[luismadriz]

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:

Students:

Load *,

     Left(Session,4) as YearStart;

Load

    "S/N",

    Name,

    Sex,

    School,

    Session,

    "Course of Study",

    Duration

From [lib://AttachedFiles/Scholarship.xlsx]

(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)

Temp:

Load Min(YearStart)          as MinYear,

     Max(YearStart+Duration) as MaxYear

Resident Students;

Let vMinYear = Peek('MinYear', 0, 'Temp'); 

Let vMaxYear = Peek('MaxYear', 0, 'Temp'); 

Drop Table Temp;

// Create Table with Years

Years:

Load

  ($(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');

Control:

Load

  $(vStudentNumber) as "S/N",

  ($(vYearStart)+IterNo()-1) & '/' & ($(vYearStart)+IterNo()) as SessionYear

AutoGenerate 1 While IterNo() <= $(vDuration);

Next vCounter;

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,

Cheers,

Luis

[akpofureenughwu]

stalwar1‌ ivan.bozov‌ roharoha‌

Please can you make a contribution on this?

[antoniotiman]

See Attachment

[antoniotiman]

You can also add in Script

IntervalMatch(EliSession) LOAD YearStart,YearEnd Resident TableA;

Then Expression

Sum(Fee)

[akpofureenughwu]

luismadriz‌

Thank you. This is excellent.

[Anonymous]

DATA:
LOAD [S/N],
Name,
Sex,
School,
num(subfield(Session,'/',1)) as begin,
Rangesum(num(subfield(Session,'/',1)),Duration) as end,
[Course of Study],
Duration
FROM
[mock scholarship data.xlsx] (ooxml, embedded labels, table is Sheet1);


for i= 2000 to 2010
session_calendar:
load $(i) as session AutoGenerate 1;
NEXT

IntervalMatch (session) LOAD begin, end Resident DATA;

[luismadriz]

Cheers,

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).

Cheers,

Luis