topic Re: Filter Pane - IF Statement doesn't work there in App Development

Filter Pane - IF Statement doesn't work there

lovelyzai — Wed, 15 Jun 2016 21:44:28 GMT

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!

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

swuehl — Wed, 15 Jun 2016 22:22:28 GMT

Try a filter pane field expression like

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

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

lovelyzai — Thu, 16 Jun 2016 02:13:47 GMT

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:

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).
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.

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

swuehl — Thu, 16 Jun 2016 09:38:25 GMT

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.

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

lovelyzai — Thu, 16 Jun 2016 14:30:30 GMT

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!

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

swuehl — Thu, 16 Jun 2016 14:34:18 GMT

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 ...;

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

lovelyzai — Thu, 16 Jun 2016 15:02:33 GMT

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!

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

swuehl — Thu, 16 Jun 2016 15:20:05 GMT

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?

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

lovelyzai — Thu, 16 Jun 2016 15:34:45 GMT

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:

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

Angela_Zhou — Fri, 30 Nov 2018 18:51:32 GMT

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,