Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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:

NameTennisFootballSquashCyclingAthletics
BobYYNYN
BillNNNYN
SimonYYYYN
JamesYNYYN
JaneNNYYN

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!

1 Solution

Accepted Solutions
Not applicable

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

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.

4 Replies

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

HI

PFA

Not applicable

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

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

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

HI

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

Not applicable

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

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.

Community Browser