Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
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
Highlighted
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.

View solution in original post

4 Replies
Highlighted

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

HI

PFA

Highlighted
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 🙂

Highlighted

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

HI

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

Highlighted
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.

View solution in original post