topic Re: Scholarship Table Analysis in QlikView

Scholarship Table Analysis

akpofureenughwu — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Scholarship Table Analysis

luismadriz — Mon, 13 Nov 2017 01:57:29 GMT

Hi Akpofure,

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

Cheers,

Re: Scholarship Table Analysis

akpofureenughwu — Mon, 13 Nov 2017 04:29:57 GMT

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

Re: Scholarship Table Analysis

luismadriz — Mon, 13 Nov 2017 06:38:37 GMT

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

Re: Scholarship Table Analysis

akpofureenughwu — Mon, 13 Nov 2017 08:17:39 GMT

stalwar1‌ ivan.bozov‌ roharoha‌

Please can you make a contribution on this?

Re: Scholarship Table Analysis

antoniotiman — Mon, 13 Nov 2017 09:02:07 GMT

See Attachment

Re: Scholarship Table Analysis

antoniotiman — Mon, 13 Nov 2017 10:02:18 GMT

You can also add in Script

IntervalMatch(EliSession) LOAD YearStart,YearEnd Resident TableA;

Then Expression

Sum(Fee)

Re: Scholarship Table Analysis

akpofureenughwu — Mon, 13 Nov 2017 12:49:13 GMT

luismadriz‌

Thank you. This is excellent.

Re: Scholarship Table Analysis

Anonymous — Mon, 13 Nov 2017 19:44:06 GMT

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;

Re: Scholarship Table Analysis

luismadriz — Mon, 13 Nov 2017 23:02:53 GMT

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

Re: Scholarship Table Analysis

akpofureenughwu — Wed, 07 Mar 2018 15:27:30 GMT

antoniotiman mancinitiman stalwar1‌

Hello sir,

I just discovered a bug in this script.

For the Eligible session, It gives the exact number +1 year

For Flower Kepp's Profile in the attached app, His course duration is four year ie 2000/2001, 2001/2002, 2002/2003, 2003/2004

But if you filter with the Eligible filter, 2004/2005 will be included and this is not correct

Can you help me out

Regards