Qlik: Ignoring Selections for Entire Tables

 

There are a ton of ways to accomplish the many tasks you might use in set analysis. I have figured out some favorites that can be used in an application with just a simple copy and paste of some variables and then using them in your expressions.

 

Sometimes you will find that you want to ignore all the selections on an entire table so you could manually list each field in your expression like this…

 

 

I use a standard calendar  that has 30 fields and manually coding all of that would be error prone and tedious and it would obscure the things you are actually trying to accomplish in  your expression. To save time, you can use an expression over the system data model to  automate the building of the field selection overrides. That expression uses the concat function to string all the field names together, like this…

 

You can use dollar sign expansion in your set analysis expression to insert this into your expression

 

So that is wonderful! You can override the selections for the entire table but are other developers going to have ANY clue what you are doing? Probably not. So if you put the expression into a variable then you can override selections on the calendar table in any expressions with some simple variable references expressions.

Finally what if I don’t want to just override the calendar but I want to be able to override the selections on any table. Well I have 300 tables in my data model and that would be a ton of variables and frankly the data model could change and screw up all those variables. So because I’m a little lazy and very nerdy I learned that you can add parameters to variables to make them dynamic.

 

So here is the expression we will add to a new variable that will allow us to override selections on any table we choose.

 

You’ll notice the “$1” in the expression that part of the expression is a parameter, you can pass the name of a table to the expression and override those selections on any table. You’ll also notice I removed the equal sign from the beginning of the expression, more on this later.

 

Now when I call the variable I have to do it in a two step process that I am calling “double dollar sign expansion”

The reason for the double dollar sign expansion is because I have to insert the parameter into the string and then evaluate the expression in separate steps otherwise the expression doesn’t parse but WOW that is pretty cool!!!

 

The next thing I want to talk about is the opposite case: ignoring selections on all of your tables except 1. The problem with this case is you would have to call our magical variable for ALL of our tables which would be easy if you didn’t have very many tables. But I have another solution that finds it’s roots deep in the QlikView help pages for Alternate States. When writing expressions with alternate states you can make some fields obey the default state or any other alternate state by naming the state and field in the operand of the set modifiers. It looks like this…

 

So in this example I am setting my OrderDate Year to obey the selections of the default state OrderDate Year and the OrderNumber to the alternate state named CompareState. So stay with me for a minute, I am not talking about creating alternate states I am simply pointing out some set analysis expressions that I don’t think are widely used. So if you want to make your expression obey only one table then you can use the same concepts we talked about earlier but using this alternate state syntax.

 

This is the variable I’m calling saObeyTable

 This is the expression where I call variable

So we start by making the entire set analysis expression ignore all selections by adding the set identifier “1” and then calling our variable which strings together all the fields from the Orders table.

 

I hope these tricks will save you some time and make your apps better.