topic How do I hide rows when Null value exist? in QlikView

How do I hide rows when Null value exist?

joe_nguyen — Fri, 14 May 2010 22:40:52 GMT

What setting in the document allows me to dynamically hide rows in a table where one of the cell values = null?

Thanks,

Joe

How do I hide rows when Null value exist?

johnw — Fri, 14 May 2010 22:53:14 GMT

If you mean a DIMENSION cell value, Properties -> Dimensions -> select the dimension -> Suppress When Value Is Null.

If you mean an EXPRESSION cell value, and there's only one expression, Properties -> Presentation -> Suppress Zero-Values.

If you mean an EXPRESSION cell value, and there is more than one expression, and you want to hide the row if ANY of the expressions are null, that's when things get complicated. But I won't try to answer that unless that's what you meant.

How do I hide rows when Null value exist?

joe_nguyen — Sat, 15 May 2010 00:49:50 GMT

It's the third, most complicated one.

Thanks,

Joe

How do I hide rows when Null value exist?

johnw — Sat, 15 May 2010 01:11:03 GMT

Ah, OK. Well, then let's say your table is currently this:

Dimension = Customer
Expression 1 = sum(Amount)
Expresison 2 = avg(Quantity)

This is one way to make them both disappear if either is null (untested):

Dimension = Customer
Expression 1 = if(len(sum(Amount)) and len(avg(Quantity)),sum(Amount))
Expression 2 = if(len(sum(Amount)) and len(avg(Quantity)),avg(Quantity))

And here's another way (also untested):

Dimension = aggr(if(len(sum(Amount)) and len(sum(Quantity)),Customer),Customer)
Expression 1 = sum(Amount)
Expresison 2 = avg(Quantity)

How do I hide rows when Null value exist?

joe_nguyen — Sun, 16 May 2010 12:02:24 GMT

John, thanks for the excellent suggestion. However, I found out that one of my expressions is using Rank, so it looks like the setting to hide 0 / null is being disregarded by QV.

Any other suggestion?

Re: How do I hide rows when Null value exist?

manisha10 — Thu, 02 Sep 2021 12:01:04 GMT

Hi John,

This expression is not working for me!

A	B	Actual	Target	Difference	MaxValue
Commercial	Unsecured	20	11	9	9
Consumer	Secured	11	11	0	-
Consumer	Unsecured	5	11	-6	-
Commercial	Secured	4	11	-7	-

Dimensions: A,B,Actual, Target

Measure: MaxValue = if(fabs(Actual-Target)=max(Total(fabs(Actual-Target))),fabs(Actual-Target),null())

Expressions used for Dimensions is like this:

= aggr(if(len([MaxValue]),A),A)

It is showing error.

Error:

A	B	Actual	Target	Difference	MaxValue
-	Unsecured	20	11	9	9
-	Secured	11	11	0	-
-	Unsecured	5	11	-6	-
-	Secured	4	11	-7	-