topic Re: Independent Listbox without Alternate State in QlikView

Independent Listbox without Alternate State

Wed, 25 Nov 2020 16:16:04 GMT

Hey,

I have a list box where its fields are dependent on the selection of another listbox.

In that listbox I want to display only sector specific measures dependent on the selection I made under sector. Each measure is assigned to its sector.

When I use this formula to filter my field and make a selection the listbox of sectors will also be affected. Is it possible to use the measure listbox independently except of using alternate states?

=if(GetFieldSelections(sector, ',', 10000, '$')='health',If(match([_Metricsector],'All','health'),_Metric,null()),
if(GetFieldSelections(sector, ',', 10000, '$')='industry',If(match([_Metricsector],'All','industry'),_Metric,null()),
if(GetFieldSelections(sector, ',', 10000, '$')='financial',If(match([_Metricsector],'All','financial'),_Metric,null()),null())))

Measure assignment is as follows:

LOAD * INLINE [

_Metric, _Metriksector

KPI1, All

KPI2, health

KPI3, industry

KPI4, financial

];

Re: Independent Listbox without Alternate State

sunny_talwar — Wed, 29 Jun 2016 12:06:33 GMT

Not sure I understand why you need this independent listbox without using alternate state? Can you elaborate as to what you are trying to do?

Re: Independent Listbox without Alternate State

Wed, 29 Jun 2016 12:09:43 GMT

I do not want to use alternate state as it is not supported in NPrinting OnDemand, which I want to use.

The Listbox is just for condition purposes in a table, so which expressions to display depending on the selections made in the listbox.

Re: Independent Listbox without Alternate State

sunny_talwar — Wed, 29 Jun 2016 12:12:02 GMT

So functionally, what are you trying to accomplish here?

Re: Independent Listbox without Alternate State

Wed, 29 Jun 2016 12:17:44 GMT

As mentioned in my first post I do not want this listbox to affect the other listbox (sector).

Currently it does, for example when I select a measure which is assigned to all sectors any sector will be selected in the sector listbox. I hope this helps.

Re: Independent Listbox without Alternate State

sunny_talwar — Wed, 29 Jun 2016 12:19:07 GMT

I kind of understand your issue here, but for better understanding would you be able to share a sample?

Re: Independent Listbox without Alternate State

Wed, 29 Jun 2016 12:38:25 GMT

Depending on your current data model, would you be able to load the same field again in a separate table that isn't linked to the table containing the field you don't want to be affected? You would basically be creating the same field again in a separate table specifically for the list box. Not the most efficient method but since you're unable to use alternate states it's an idea.

Re: Independent Listbox without Alternate State

Wed, 29 Jun 2016 12:44:55 GMT

See attached.

Selecting the sector displays the right corresponding KPIs in the Measures Listbox, but when selecting KPI1 which is assigned to to any sector any entry will be selected in the sector listbox subsequently. In the consequence any company is displayed in the table and I cannot select a measure anymore.

Re: Independent Listbox without Alternate State

Wed, 29 Jun 2016 12:48:30 GMT

Mhh I understand you suggestion, but then I would need to create two listboxes for sector and always make a selection twice, right? One for the "normal" working data and one to get the right measures displayed?!

Re: Independent Listbox without Alternate State

Digvijay_Singh — Wed, 29 Jun 2016 13:04:08 GMT

I am not sure but it may be the case that your data model needs some improvement, I am not able to understand why do you have separate fields Sector and _MeticSector, the content looks to be same. Is there any possibility to associate Sector to _Metric?

Also looks like typo, your field in script is _Metriksector, but you have used _Metricsector in the formula.

If you are not able to share sample, I tried to prepare one, can you rephrase ur need with reference to attached sample -

Re: Independent Listbox without Alternate State

Wed, 29 Jun 2016 13:35:04 GMT

I have provided an example above.

Maybe it does, however, I will try to explain the difference of sector and _Metricsector:

sector: A sector like financial, health, etc. is assigned to any company in the overall data model.

_Metricsector: This only gives each KPI the property for which sector it is valid. Some are only valid for certain sectors, some are valid cross-sectorally.

So depending on the sector of the company the valid KPIs will be displayed. If a KPI is valid cross-sectorally it will be displayed whatever the sector of the company is. Be aware there is no sector "All", it could also be "tomato". It is just a place holder for the purpose of filtering basically.

It is quite tough to explain, I hope it became clear.

Re: Independent Listbox without Alternate State

Digvijay_Singh — Wed, 29 Jun 2016 15:58:39 GMT

I could recreate your problem. See the attached updated sample, if you select KPI, it won't impact sector now.

Re: Independent Listbox without Alternate State

Thu, 30 Jun 2016 08:12:17 GMT

Ok one more question regarding this:

When I use this expression:

=SubStringCount(Concat(_Metric, '|'), 'KPI2')

It shows a one anyway, whether it is currently displayed in the listbox or not.

Can I reduce the StringCount to the filtered _Metric Listbox instead of the complete values of _Metric?

Re: Independent Listbox without Alternate State

Digvijay_Singh — Thu, 30 Jun 2016 08:25:09 GMT

If you use the same expression here, it works, I tried as -

=SubStringCount(Concat(

Aggr(Only({1}if(GetFieldSelections(Sector, ',', 10000, '$')='Health',If(match([_Metricsector],'All','Health'),_Metric,null()),

if(GetFieldSelections(Sector, ',', 10000, '$')='Industry',If(match([_Metricsector],'All','Industry'),_Metric,null()),

if(GetFieldSelections(Sector, ',', 10000, '$')='Financial',If(match([_Metricsector],'All','Financial'),_Metric,null()),null())))),_Metric), '|'), 'KPI2')