topic Re: "Suppress when value if null" for expressions in QlikView

"Suppress when value if null" for expressions

Anonymous — Fri, 12 Sep 2014 10:04:49 GMT

Hi, all,

Now I am working to filter the data based on expression columns for nprinting.

In the qlikview table, we need to show all the data, and for nprinting report purpose, we need to select the revenue>=100.

After I use the variable and expressions as below to make the expression column null:

=IF(($(vRev)=1 and SUM(HourRevenue)<100),NULL(),SUM(HourRevenue))

They are still in the table, I am wondering is there an option like "Suppress when value if null" for dimensions to hide the whole row when this expression column is "null"?

Thanks

Re: "Suppress when value if null" for expressions

ecolomer — Fri, 12 Sep 2014 10:10:24 GMT

Yes, you can put in a dimension the expresion if(IsNUll( xxx), ....) and select the option supres if is null

Re: "Suppress when value if null" for expressions

PrashantSangle — Fri, 12 Sep 2014 10:13:32 GMT

Hi,

You can use

Isnull(fieldName)

len(fieldName)>0

Regards

Re: "Suppress when value if null" for expressions

Anonymous — Fri, 12 Sep 2014 10:19:45 GMT

Hi, Enrique,

Sorry for confusing you. But I think you do not understand my question well.

I do not need to hide the row based on the "Dimensions", I need to hide the row based on "Expressions". So the "Suppress when value if null" in "Dimensions" tab dose not work.

Re: "Suppress when value if null" for expressions

PrashantSangle — Fri, 12 Sep 2014 10:24:19 GMT

Hi,

In Presentation tab you have Supress missing and Spress Zero Values.

Try it.

Regards

Re: "Suppress when value if null" for expressions

Anonymous — Fri, 12 Sep 2014 10:25:16 GMT

Hi, Max,

Sorry for confusing you as well. I do not need to hide the data base on "fieldName". As it is a column from "Expression" tab. It can not be used in the "Calculated Dimension", and the "Enable Conditional" in Dimensions tab.

Re: "Suppress when value if null" for expressions

PrashantSangle — Fri, 12 Sep 2014 10:31:44 GMT

Hi,

If you have only one expression then

In presentation tab you have Supress missing and Supress Zero Values

They work as per your requirement.

Regards

Re: "Suppress when value if null" for expressions

ecolomer — Fri, 12 Sep 2014 10:46:43 GMT

But you need to supres the column?

Or supress the row when the value of column is null?

In this last case you put in the dimension the expresion:

if(IsNull(expresion, Null(), Dimension)

and select the option to supress if is null

Re: "Suppress when value if null" for expressions

Anonymous — Fri, 12 Sep 2014 11:17:37 GMT

Hi, Enrique,

I need to suppress the whole row.

I tired to use expression as below in Calculated Dimension:

=IF(IsNull(IF(($(vRev)=1 and SUM(HourRevenue)<100),NULL(),SUM(HourRevenue))), null(),$(vdimension))

But it dose not work.

Am I doing somthing wrong?

Re: Re: "Suppress when value if null" for expressions

Anonymous — Fri, 12 Sep 2014 12:17:02 GMT

Hi, Max,

I have more than one expression.

Hi, Piet,

It is strange that the last reply from Piet is gone.

This solution works when there is one expression column. But dose not work for multiple expression columns.

See the example I reused from Piet. I would like to select the table when REVENUE>100, when CONV column still have data.

Re: "Suppress when value if null" for expressions

Anonymous — Fri, 12 Sep 2014 12:33:52 GMT

hi, all,

Thanks for all your answers.

As it is possible to suppress data based on Dimension column, it should also be possible to suppress data based on Expression columns.

Finally I used a dirty solution. But always welcome if there is anything better.

I modify to make other expression columns null if the column REVENUE is null. Then the rows for REVENUE can be filter out.

BTW, AGGR() is not the trick. And you can just use normal SUM(..), not AGGR(SUM(...),Dim)

Re: "Suppress when value if null" for expressions

oboute — Tue, 20 Sep 2016 08:26:19 GMT

Hello,

Thank's for the trick !

I was stuck like you and i did the same (others expressions = 0 when my expression =0).

Did you found something less dirty ?

Re: "Suppress when value if null" for expressions

Anonymous — Tue, 20 Sep 2016 15:52:53 GMT

Hi, Olivier,

Unfortunately, I did not find other solution.

Zhihong