Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
with IF: If (Data_Tab_Elements = 'Category',Data_Strata)
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. 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.
Try a filter pane field expression like
=Aggr(If (Data_Tab_Elements = 'Category',Data_Strata),Data_Strata)
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. 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.
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!
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 ...;
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!
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?
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:
The one with Aggr and Only shows as dark gray - Excluded: