Creating a List Box of Field Headers from Flag/Indicator Fields

[#1]

I have some data to load where there are five Y/N flag fields.  How can I create ONE List Box that shows the 5 Field Names (rather than Y or N) and allows multiple options to be selected?  Example Data:

Name	Tennis	Football	Squash	Cycling	Athletics
Bob	Y	Y	N	Y	N
Bill	N	N	N	Y	N
Simon	Y	Y	Y	Y	N
James	Y	N	Y	Y	N
Jane	N	N	Y	Y	N

Once in Qlikview I would like a List Box that lists the sports eg:

Sport
Tennis
Football
Squash
Cycling
Athletics

So if 'Football' was selected, my Chart/Table would show Bob & Simom.  And if 'Football' AND 'Tennis' was selected I would be Bob, Simon & James.

(The data actually has a a load more Flag fields and other columns, so I don't want to load as a CrossTable as figured that would be slow and inefficient)

Thanks for your help!

[MayilVahanan]

HI

PFA

[#3]

Thanks for the quick response Mayil!  My problem with the CrossTab is that now my Datatable has 25 rows of data instead of 5.  And when I run other Charts/Queries for say Bob to find his address, or year group etc(I have a lot of other fields), there are now 5 lines for Bob (and all these additional fields will be repeated).  And I'll have to use think about when to use 'distinct' and when not.

I was hoping there might be a clever way using Set Analysis or Variables 🙂

[MayilVahanan]

HI

Inorder to avoid the repeated field, use distinct in your expression

[#5 · Accepted solution]

So in the end I used the CrossTable feature, but only for my Flag columns, effectively creating a separate lookup table, which joined back to my original data on the NameID.  In my new Flag-Lookup-Table I added a Where Clause so it only stored the 'Y's.  (used the this thread to help with that - eg utilised temp tables)

This saves having to write set analysis in every chart (Flag = "Y"), and gives a clean soloution when building out the dashboard.