Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

lovelyzai
New Contributor II

Filter Pane - IF Statement doesn't work there

Hi guys,

If you could give me some advise here, I would be very appreciated - why IF statement doesn't work in filter pane?

I have a table with 2 columns - "Data_Tab_Elements" and "Data_Strata".

I'd like to show "Data_Strata" ('a','b','c','d') in filter pane, ONLY when "Data_Tab_Elements" is 'Category'. However, whenever I included IF statement in Filter Pane, it doesn't work. Qlik would not recognize the selection (Showed in "GetSelectedCount(Data_Strata)" as belowSmiley Happy


I create an app to show the differences between without IF vs. with IF.


without IF: Data_Strata

no_if.PNG


with IF: If (Data_Tab_Elements = 'Category',Data_Strata)

with_if.PNG


To my best knowledge, Filter Pane is set as "DIMENSION", which should allow to use IF statement. 

Any helps would be greatly appreciated!

Thanks in advance!

1 Solution

Accepted Solutions
MVP
MVP

Re: Filter Pane - IF Statement doesn't work there

1. If I understood you correctly, the virtual table is not joined back to the source table

How many Data_Strata values will your final model show? and are Data_Strata and Data_Tab_Elements residing in the same table in your model or in different tables?

2. Instead of my first field expression, you can use

=Aggr(Only({<Data_Tab_Elements = {Category},Data_Strata= >} Data_Strata),Data_Strata)

This should avoid the issue with not showing excluded values and could potentially perform better on larger sets of Data_Strata.

8 Replies
MVP
MVP

Re: Filter Pane - IF Statement doesn't work there

Try a filter pane field expression like

=Aggr(If (Data_Tab_Elements = 'Category',Data_Strata),Data_Strata)

lovelyzai
New Contributor II

Re: Filter Pane - IF Statement doesn't work there

Thanks Swuehl! I

have tried your solution. This solution absolutely resolved the problem "IF Statement doesn't work in filter pane." However, it also brings me couple of concerns towards this solution:

  1. Please feel free to correct me if I'm wrong. But to my best knowledge, the Aggr() function would create a "virtual table" that has the .rows with "Data_Tab_Elements" = 'Category', and Join back on the actual source table. This would have more obvious impacts on the performance of the app with the growth of datasets (ex. I do have mass rows in production environment).
  2. As the Join results, it would only show one selection after one value is selected (only show that value). We won't be able to do multiple chooses on values.

one_choice.PNG

MVP
MVP

Re: Filter Pane - IF Statement doesn't work there

1. If I understood you correctly, the virtual table is not joined back to the source table

How many Data_Strata values will your final model show? and are Data_Strata and Data_Tab_Elements residing in the same table in your model or in different tables?

2. Instead of my first field expression, you can use

=Aggr(Only({<Data_Tab_Elements = {Category},Data_Strata= >} Data_Strata),Data_Strata)

This should avoid the issue with not showing excluded values and could potentially perform better on larger sets of Data_Strata.

lovelyzai
New Contributor II

Re: Filter Pane - IF Statement doesn't work there

1. You're right! Yes, the virtual table is not "Join back to the source table". My bad.

There are about 7,000 unique Data_Strata values sit on about 500MM rows, which increases another 70MM rows every month.

2. Great! I didn't see any noticeable impacts on the app. Thank you so much! Only a tiny imperfection is that, once a value is selected, other values will become gray, which look like "excluded". But good news is that user can still select other values.

Thank you again!

works!.PNG

MVP
MVP

Re: Filter Pane - IF Statement doesn't work there

As another option, you can create another field in your load script:

LOAD Data_Tab_Elements,

          Data_Strata,

          If (Data_Tab_Elements = 'Category',Data_Strata) as Data_Strata_Cat,

          ....

FROM ...;

lovelyzai
New Contributor II

Re: Filter Pane - IF Statement doesn't work there

That also works. I assume it would increase the app size a little bit, but shouldn't be very bad. Both work around resolve my original problem.

Thank you!

MVP
MVP

Re: Filter Pane - IF Statement doesn't work there

2. Great! I didn't see any noticeable impacts on the app. Thank you so much! Only a tiny imperfection is that, once a value is selected, other values will become gray, which look like "excluded". But good news is that user can still select other values.

Isn't this Qlik default?

lovelyzai
New Contributor II

Re: Filter Pane - IF Statement doesn't work there

Yes, it's default. But the gray is kind of different, right? I mean, light gray means Alternative choice, and dark gray means Excluded.

So the expression without any IF statement, the choice is light gray - Alternative:

light_gray.PNG

The one with Aggr and Only shows as dark gray - Excluded:

dark_gray.PNG