topic Re: Help me redesign a table for qlikview selections in QlikView

Help me redesign a table for qlikview selections

vishnus85 — Thu, 05 Sep 2013 13:17:49 GMT

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

Re: Help me redesign a table for qlikview selections

Anonymous — Thu, 05 Sep 2013 16:02:59 GMT

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

Re: Help me redesign a table for qlikview selections

jpapador — Thu, 05 Sep 2013 16:06:05 GMT

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.

Re: Help me redesign a table for qlikview selections

vishnus85 — Fri, 06 Sep 2013 15:35:33 GMT

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.

Re: Help me redesign a table for qlikview selections

vishnus85 — Fri, 06 Sep 2013 15:43:02 GMT

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