Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

akpofureenughwu
Contributor III

Scholarship Table Analysis

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/NNameSexSchoolSessionCourse of StudyDuration
1John BrownMaleJim High School2000/2001Fine Art6
2Eze FrancisMaleSt. Pauli University2001/2002Engineering5
3Dede ChiFemaleSt. Pauli University2002/2003Medicine6
4Flowers KeppFemaleSt. Pauli University2000/2001Geology4

Thank you

10 Replies
luismadriz
Valued Contributor

Re: Scholarship Table Analysis

Hi Akpofure,

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

Cheers,

L

akpofureenughwu
Contributor III

Re: Scholarship Table Analysis

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
Valued Contributor

Re: Scholarship Table Analysis

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:

DataModel.png

Which will easily let you build a filter pane (Qlik Sense jargon) with the Years and a straight table with the Students

Charts.png

And when selection 2007/2008 you'll see this:

Selection.png

Please try and keep me posted,

Cheers,


Luis

akpofureenughwu
Contributor III

Re: Scholarship Table Analysis

stalwar1ivan.bozovroharoha

Please can you make a contribution on this?

Highlighted
antoniotiman
Honored Contributor III

Re: Scholarship Table Analysis

See Attachment

antoniotiman
Honored Contributor III

Re: Scholarship Table Analysis

You can also add in Script

IntervalMatch(EliSession) LOAD YearStart,YearEnd Resident TableA;

Then Expression

Sum(Fee)

akpofureenughwu
Contributor III

Re: Scholarship Table Analysis

luismadriz

Thank you. This is excellent.

robin_hausdoerfer
Valued Contributor III

Re: Scholarship Table Analysis

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
Valued Contributor

Re: Scholarship Table Analysis

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