# New to QlikView

Discussion board where members can get started with QlikView.

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/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

10 Replies
Valued Contributor

## Re: Scholarship Table Analysis

Hi Akpofure,

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

Cheers,

L

Contributor III

## Re: Scholarship Table Analysis

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

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:

Left(Session,4) as YearStart;

"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:

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:

(\$(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:

\$(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

Contributor III

## Re: Scholarship Table Analysis

Please can you make a contribution on this?

Highlighted
Honored Contributor III

## Re: Scholarship Table Analysis

See Attachment

Honored Contributor III

## Re: Scholarship Table Analysis

You can also add in Script

Then Expression

Sum(Fee)

Contributor III

## Re: Scholarship Table Analysis

Thank you. This is excellent.

Valued Contributor III

## Re: Scholarship Table Analysis

DATA:
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;

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