Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lovelyzai
Partner - Contributor II
Partner - 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 below:)


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
swuehl
MVP
MVP

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.

View solution in original post

9 Replies
swuehl
MVP
MVP

Try a filter pane field expression like

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

lovelyzai
Partner - Contributor II
Partner - Contributor II
Author

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

swuehl
MVP
MVP

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
Partner - Contributor II
Partner - Contributor II
Author

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

swuehl
MVP
MVP

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
Partner - Contributor II
Partner - Contributor II
Author

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!

swuehl
MVP
MVP

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
Partner - Contributor II
Partner - Contributor II
Author

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

Angela_Zhou
Contributor III
Contributor III

Swuehl,
Thank you so much for this. It solved our issue when most of our calculation not working due the the date filter with if condition in it.

Angela,
Angela Z.