topic Re: Straight Table issue: Problem in null values in the expression in QlikView

Straight Table issue: Problem in null values in the expression

Mon, 07 Nov 2011 20:29:39 GMT

Hi All,

I have a striaght table with 2 dimensions..and have 6 expressions. My requirement is to show only Non-Null values for one of my expression...but even when I go to presentation tab, and Suppress null values for that expression, it still shows the values and there is no change in the table

Does anyone have expreinced a similar problem? Any solutions would be appreciated.

Thanks in advance.

Re: Straight Table issue: Problem in null values in the expression

johnw — Mon, 07 Nov 2011 23:37:04 GMT

When you suppress nulls, it will still show the row if ANY of your expressions is non-null. Are you trying to suppress the entire row if the first expression is null, even if there are values in the other expressions? You can do something like this:

Sales = sum(Sales)
Revenue = if(Sales,sum(Revenue))
Cost = if(Sales,sum(Cost))
Profit = if(Sales,Revenue - Cost)
etc.

Another way would be to use a calculated dimension. Let's say your dimension is customer. You could instead do this, I believe, and then suppress nulls for the dimension instead of the expression. That keeps your expressions simpler.

Dimension = aggr(if(sum(Sales),Customer),Customer)

Re: Straight Table issue: Problem in null values in the expression

Tue, 08 Nov 2011 00:18:54 GMT

Hi John,

thanks for replying. I am using expressions like:

Expr1: Calcualting something on dimesnion

Expr2: calculations + conditions like if Expr1= 'ABC'

Expr3: using Expr1 and Expr 2

Expr4: using Expr1,2,3,

So, my requirement is to show only those rows in my straight table where the Expr4 does not contain any Null nodes. Basically, removing the entire row, if Expr4 is NULL, irrespctive of the values of Expr 1 , Expr2 or Expr3.

My expr5 looks like:

=if(Expr1='True' AND Expr2='True' AND Expr3='True' AND Expr5='True','True')

So, i want only that rows where the value of this expr5 is not null. I can't create a list and then select, because its an expression.

Thanks. Please let me know, if you want me to be more specific.

Thanks again.

Re: Straight Table issue: Problem in null values in the expression

johnw — Tue, 08 Nov 2011 17:29:15 GMT

In the if() for your other expressions, you'd need to expand the expression for Expr5, not just write it using the other expressions. Since an expanded expression for Expr5 would be complicated, I'd probably write it as a variable. Then use that variable when deciding whether or not to calculate the other expressions. Something like this.

Expr1: if($(vExpr5)='True',calculate something on dimension)
Expr2: if($(vExpr5)='True',calculations + conditions like if Expr1='ABC')
Expr3: if($(vExpr5)='True',using Expr1 and Expr2)
Expr4: if($(vExpr5)='True',using Expr1 2 3)
Expr5: $(vExpr5)

The calculated dimension approach would probably be similar. With three dimensions:

Dim1: aggr(if($(vExpr5)='True',Dim1),Dim1,Dim2,Dim3)
Dim2: aggr(if($(vExpr5)='True',Dim2),Dim1,Dim2,Dim3)
Dim3: aggr(if($(vExpr5)='True',Dim3),Dim1,Dim2,Dim3)

This is very untested and could be wrong. Think of it more as the first thing I'd try testing if this were my own application.

Re: Straight Table issue: Problem in null values in the expression

Tue, 08 Nov 2011 19:03:17 GMT

Thanks for replying John. So, in the Variable vExpr5, should I write the actual expression(Expr5) and then use that variable in all the expressions or dimensions?

like vExpr5= if(Expr1='True' and Expr2='True' and Expr3=True') something like that. Is that what you meant?

Thanks again.

Re: Straight Table issue: Problem in null values in the expression

johnw — Tue, 08 Nov 2011 19:25:50 GMT

Yes and no. You need to fully-expand all of the expressions or you hit a logical loop. Let's take a simple example. Let's say you currently have this:

Exp1: if(sum(Sales)>500,'True','False')
Exp2: if(Exp1='True' or sum(Cost)<400,'True','False')
Exp3: if(sum({<Product={'Apple'}>} Cost)>500,'True','False')
Exp4: if(Exp1='True' and Exp2='True' and Exp3='True','True')

Now you only want to show any of these expressions if Exp4 is 'True'. So we create a variable:

vExp4 = if(Exp1='True' and Exp2='True' and Exp3='True','True')

And we reference it in the other expressions, such as this:

Exp1: if($(vExpr)='True',if(sum(Sales)>500,'True','False'))

QlikView will do dollar sign expansion:

Exp1: if(if(Exp1='True' and Exp2='True' and Exp3='True','True')='True',if(sum(Sales)>500,'True','False'))

And now the expression for Exp1 depends on the result from Exp1. We can't calculate it before we calculate it, so this is an impossible situation. So we instead need to fully-expand the expressions inside the variable:

vExp4 = if(if(sum(Sales)>500,'True','False')='True' and if(if(sum(Sales)>500,'True','False')='True' or sum(Cost)<400,'True','False')='True' and if(sum({<Product={'Apple'}>} Cost)>500,'True','False')='True','True')

This can be simplified to this:

vExp4 = if(sum(Sales)>500 and sum({<Product={'Apple'}>} Cost)>500,'True')

And then you use it in the expressions:

Exp1: if($(vExp4)='True',if(sum(Sales)>500,'True','False'))
Exp2: if($(vExp4)='True',if(Exp1='True' or sum(Cost)<400,'True','False'))
Exp3: if($(vExp4)='True',if(sum({<Product={'Apple'}>} Cost)>500,'True','False'))
Exp4: $(vExp4)

Or you might use variables for the other expressions if you think it would be easier to maintain. That basically automates the full expansion, but isn't clever enough to simplify once fully-expanded.

vExp1: if(sum(Sales)>500,'True','False')
vExp2: if($(vExp1)='True' or sum(Cost)<400,'True','False')
vExp3: if(sum({<Product={'Apple'}>} Cost)>500,'True','False')
vExp4: if($(vExp1)='True' and $(vExp2)='True' and $(vExp3)='True','True')

Exp1: if($(vExp4)='True',$(vExp1))
Exp2: if($(vExp4)='True',$(vExp2))
Exp3: if($(vExp4)='True',$(vExp3))
Exp4: $(vExp4)

Again, I may well have some things incorrect. If so, hopefully at least the basic idea of what I'm proposing is clear.

It sure would be nice if all that wasn't necessary, though, and could be handled with a simple checkmark. Maybe I'm missing something easier. Anyone else have any ideas?

Re: Straight Table issue: Problem in null values in the expression

Tue, 08 Nov 2011 23:37:53 GMT

Alright John, I'm gonna try the above solution and see if it works. Thanks a lot. Any other suggestions would be appreciated.

Thanks again.

Straight Table issue: Problem in null values in the expression

Sun, 11 Mar 2012 20:21:47 GMT

hi,

I've similar requirement...when the user enters a value for a particular expression in input box then in the straight table only those values shud appear for that expression wich are greater than the value entered & other rows shud become hidden, the problem in applying the above solution is that i've 5 expressions and 8 dimensions to work with.

Any solution ?

Regards