topic Re: How to remove Row with null value ? in QlikView

How to remove Row with null value ?

paulyeo11 — Thu, 23 Jun 2016 05:19:22 GMT

Hi All

I like to count how many company sales go up, but the count also include those company sales =0 ,

As my column data also capture those sales = 0 , may i know how to remove those row which have 0 value ?

Now my Table have 50 Row , how to keep only 1 to 19 row ? As after 20 row all are null value.

Paul

Re: How to remove Row with null value ?

paulyeo11 — Thu, 23 Jun 2016 05:20:15 GMT

My QVW

Re: How to remove Row with null value ?

tamilarasu — Thu, 23 Jun 2016 05:24:15 GMT

Hi Paul,

Check the attachment.

Re: How to remove Row with null value ?

Thu, 23 Jun 2016 05:31:34 GMT

script write this:

if(ISNULL(company_sales) OR LEN(company_sales)=0,Null,company_sales)) AS company sales;

select suppress when value is null in dimensions.

and where ever you are calculating if(company_sales<>Null,sum(company_sales))

thanks

Khushboo

Re: How to remove Row with null value ?

paulyeo11 — Thu, 23 Jun 2016 05:39:26 GMT

Hi Tamil

I was surprise how you manage to do it ? with out modify any dimension and expression. and I was wondering how you manage to do it ? atlest i manage to figure out.

Thank you very much. it work now.

Paul

Re: How to remove Row with null value ?

paulyeo11 — Thu, 23 Jun 2016 05:40:59 GMT

Hi Khishboo

I think your solution is more correct . Appreciated.

Paul

Re: How to remove Row with null value ?

tamilarasu — Thu, 23 Jun 2016 05:57:50 GMT

Hello Paul,

Why do you want to keep 1 in expression.?

Re: How to remove Row with null value ?

paulyeo11 — Thu, 23 Jun 2016 06:02:58 GMT

Hi Tamil

I need to count how many company sales up ?

Paul

Re: How to remove Row with null value ?

paulyeo11 — Thu, 23 Jun 2016 06:06:08 GMT

Hi Vikas

Your reply is great , you should not remove it.

I want your expression to be here , So i can refer it later and try out your script , as i need to use this in situation to filter null value in SET.

Paul

reply from vikas mahajan in Scripting - View the full discussion

You can insert this in script , check null values and can skip this with set analysis

if(ISNULL(SALES_AMOUNT) OR LEN(SALES_AMOUNT)=0,1,0)) AS NullSale

Sum ({<YEAR={"$(=Max(YEAR)-1)"},NullSale={'0'}> }SALES_AMOUNT)

Re: How to remove Row with null value ?

tamilarasu — Thu, 23 Jun 2016 06:11:41 GMT

Paul,

No need to change anything. You can use this instead of expression 1.

If(Not IsNull([YTD Variance]),1,0)

Re: How to remove Row with null value ?

paulyeo11 — Thu, 23 Jun 2016 06:22:53 GMT

Hi Tamil

Thank you very much , it work fine . How about help me on below :-

How to make my Table Total amount display same value when my dimension change from COMPANY TO SOURCE ?