Help me redesign a table for qlikview selections

[vishnus85]

Hi All,

I am facing a scenario where I require 5 columns (where the value contained is Y) to show up in a listbox.

I will attach a sample scenario (excel sheet named Example) here so that I could convey my requirement more clearly. Please see the table here.

Student ID	Maths	Science	Language
1	Y	N	N
2	Y	Y	N
3	N	N	Y
4	N	Y	Y
5	Y	Y	Y
6	N	N	Y

Here Student_ID is unique for every record. The other columns are flag columns indicating whether (Y) or not (N) a student had appeared for an exam on Maths, Science and Language.

I am intending to join this table to other related tables though Student_ID field. Now my requirement is, I need a selection listbox named Exam having values as Maths, Science and Language. When I select Science, I expect the dashboard to refresh for all students who have appeared for Science exam. Same way if I select Maths and Science I expect the dashboard to refresh for all students who have appeared for Maths and Science.

I am not sure how I can achieve this but as it seems to be a very basic requirement that many experts might have came across, I would request your sincere help.

Regards,

Vishnu

[Anonymous]

Hi Vishnu,

I recommend using crossatble:

Students:

CrossTable(Subject, "Took Test?")

LOAD

[Student ID],

    Maths,

    Science,

    Language

FROM [Example.xlsx] (ooxml, embedded labels, table is Example);

See also application attached.  It is a little different than you ask, I have Y/N so you can select also students who did not take test in the selected subject.

Regards,

Michael

[jpapador]

I would suggest an IF Statement like:

If(Maths = 'Y', 'Math',

     If(Science = 'Y', 'Science',

          If(Language = 'Y', 'Language'))) as Exam

Do this in the load script and it will give you what you are looking for.

[vishnus85]

Hi Jpapador,

Thanks for the reply. But there is a small issue with that way of doing it.  There may be records where multiple subjects can have value as Y, in that case, this logic fails the purpose.

[vishnus85 · Accepted solution]

Thanks Michael,

This help me in understanding the use of CrossTable function

But I used a different method to achieve my requirement.

I defined a table SUBJECT at the script side like this

SUBJECT:

Load

[Student ID],

'Maths' as Subject

From  [Example.xlsx] (ooxml, embedded labels, table is Example)

where Maths ='Y';

Load

[Student ID],

'Science' as Subject

From  [Example.xlsx] (ooxml, embedded labels, table is Example)

where Science='Y';

Load

[Student ID],

'Language' as Subject

From  [Example.xlsx] (ooxml, embedded labels, table is Example)

where Language='Y';

Now this SUBJECT table now contains [Student ID], Subject whose exams for which the student have appeared. So when this table is joined with other tables that uses  [Student ID] as unique key, then mu purpose is fulfilled and I can use the field 'Subject' for list box.

The same could be done from your table, by defining a new table that extracts only those records where [Took Test?] = Y !

Thanks for the help