topic Display Data Set Union In List Box in QlikView

Display Data Set Union In List Box

Tue, 06 Apr 2010 01:49:40 GMT

Hello,

Here is the issue. There are model years, say 2009 and 2010. I also have months after the start of production call SOP Months. Say for model year 2009, SOP Months = 0,1,2,3,4,5,6,7,8,9,10,11,12. For model year 2010, SOP Months = 0,1,2,3,4,5,6. What I want to display in the SOP Months list box is the union of the SOP Months across model years. In this case, if both model years 2009 and 2010 are selected, the SOP Month list box should show 0,1,2,3,4,5,6 (and not 0,1...11,12). Any idea how to accomplish this in QV? It goes against the 'grain' of QV. Any help would be greatly appreciated. Thanks

Display Data Set Union In List Box

rwunderlich — Tue, 06 Apr 2010 02:18:08 GMT

It's possible, the exact approach depends on your data. If there is only one SOP Month row per Year, you could do a listbox expression like:

=if(aggr(count(Month), Month) > 1, Month)

See attached for an example. If you have futher questions, please post a litte more detail about your data.

-Rob

Display Data Set Union In List Box

Tue, 06 Apr 2010 02:41:14 GMT

I failed to mention I am unfortunately using QV 8.01; I was not able to open the attached project. There are many SOP Months rows per Year (if I understand what you are saying). Example data is below. In this case ,I reduced the data to: 2009 = 0,1,2,3,4; 2010 = 0,1,2; union = 0,1,2. Or more so, the Month list box should show only 0,1,2 when years 2009 and 2010 are selected.

Year Month

2009 0

2009 1

2009 2

2009 3

2009 4

2010 0

2010 1

2010 2

Display Data Set Union In List Box

rwunderlich — Tue, 06 Apr 2010 03:35:18 GMT

I can't recall, does 8.01 support listbox expressions?

Display Data Set Union In List Box

Tue, 06 Apr 2010 14:39:49 GMT

Yes it does; reference the General Tab->Field property (one of the top most selections 'Expression') of the List Box Properties dialog. I did try the Aggr() expression you had sent, but no values were displayed. I believe this was due to multiple rows per model year.

Display Data Set Union In List Box

Wed, 14 Apr 2010 18:13:40 GMT

Rob (or anyone), any further insight? Thanks!