topic Re: List available selection options? in QlikView

List available selection options?

Fri, 12 Sep 2014 14:45:43 GMT

I have a sheet with several list boxes. Visually I can see when I select a value in one box, how it impacts another box (unavailable options are greyed out).

Is there a way to determine the available options for a given field based on the selections placed on other fields?

I've used triggers to assign filters based on identical but differently named fields (using GetFieldSelections) but now would like to determine what options are available for a given field based on other filters being set.

Is there a way to do this?

Thank you.

Re: List available selection options?

MK_QSL — Fri, 12 Sep 2014 15:00:18 GMT

You can create all list box by using below type Expressions instead of directly selecting fieldname...

For Year List Box

Aggr(Year,Year)

For Month List Box

Aggr(Month, Month)

Now when you select any Year from Year List Box, it will only show you the associated Month and all other non-associated months would be hidden...

Re: List available selection options?

JonnyPoole — Fri, 12 Sep 2014 15:02:01 GMT

there is something called 'associative search' that is worth taking a look at.

in the caption of a list box is an icon, and if you press it , you get a search field for the list box.

if you hit the >> within the seach text you get an expanded search box which means 'associative search'. You can then search fields outside the list box and see how that will filter the values in the current list box. kind of like an indirect search.

its worth taking a look just to know its there.

Re: List available selection options?

Fri, 12 Sep 2014 15:18:36 GMT

Let me rephrase this.

Is there a way, programmatically, to determine available options? Let me explain.

I have two data sets, Summary and Detail. They are related but not so far as QV is concerned as I have deliberately named the fields differently. I need to be able to treat each independently.

Thus in the Summary Set I have ReportYear and in the Detail Set I have Report_Year. As you might imagine, the detail data set has more dimensional fields available to it - one of those is Product_Family.

The visible filters are based on the Detail data set. When they select a value in Product_Family, it automatically restricts the values in the Report_Year field in the same dataset. However, would I would like to do is determine what fields are available in Report_Year so I can filter my summary data set the same way.

I know if I do an actual selection on Report_Year, I can grab the selected values and set the same filters in ReportYear - that is in place. What I now need to do is if in the Detail table, they set an option that is NOT in the summary set but impacts the overall available value set in the summary data set, how do I grab the "available" values from the detail set so that I can set the appropriate filters in the summary set?

Perhaps an example:

Summary Set

ReportYear Unit_Sales

2011 5000

2012 5500

2013 7000

2014 5800

Detail Set

Report_Year Product_Family Unit_Sales

2011 Type A 3000

2011 Type B 2000

2012 Type A 3200

2012 Type B 2300

2013 Type A 2800

2013 Type B 2400

2013 Type C 1800

2014 Type A 2100

2014 Type B 1900

2014 Type C 1800

When the user selects Type C from Product Family listbox, I need to be able to programmatically see that only 2013 and 2014 are valid years and apply that as a filter in the Summary data set. Be able to see the available values for Report_Year so I can set those as filters for ReportYear (although they are not set as explicit filters for Report_Year but rather are implied filters based on other filters within the data set).

Make any more sense?

Thanks.

Re: List available selection options?

Fri, 12 Sep 2014 15:23:24 GMT

This does not address the specific need I have currently but I just played with that and like it a lot. Will very likely use in the future.

Re: List available selection options?

MK_QSL — Fri, 12 Sep 2014 15:26:07 GMT

As, I already replied in my first answer,,,,create a Report_Year list box with below expression

Aggr(Report_Year,Report_Year)

Re: List available selection options?

Fri, 12 Sep 2014 15:27:24 GMT

Thank you Jonathan. That is something of which I was not aware and know I'll be able to use. Doesn't quite address my immediate need but is good to know and will get used.

Re: List available selection options?

Fri, 12 Sep 2014 15:31:07 GMT

Hi Manish,

I can see that by creating the listbox as Aggr(Report_Year, Report_Year) it will show me only those values which are valid.

What I need to do is be able to grab those two values and set them as filters on my ReportYear field in the other dataset. How do I programmatically grab those two values as they are not selected values and I cannot use the GetFieldSelections() function?

For curiosity sake, I tried mixing the two fields and setting up a listbox as Aggr(ReportYear, Report_Year) to see if they would intermix. I results in a blank listbox.

Thanks!

Re: List available selection options?

MK_QSL — Fri, 12 Sep 2014 15:36:33 GMT

=Concat(Aggr(Report_Year,Report_Year),',')

Re: List available selection options?

Fri, 12 Sep 2014 15:39:55 GMT

Perfect! Thank you!

Re: List available selection options?

sebastiandperei — Fri, 12 Sep 2014 21:31:06 GMT

Qlikview shows the non-referenced data with gray background.

If you don't want to see the non-referenced values, use the Manish solution. (also, mark as useful answer, at least!!)

Obviously, all of these solutions works good ONLY IF YOUR DATA IS WELL RELATED BETWEEN THEM.

Please, send a reduced copy.