Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

How to remove Row with null value ?

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

1 Solution

Accepted Solutions
Not applicable

Re: How to remove Row with null value ?

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

10 Replies
paulyeo11
Valued Contributor II

Re: How to remove Row with null value ?

My QVW

Re: How to remove Row with null value ?

Hi Paul,

Check the attachment.

Not applicable

Re: How to remove Row with null value ?

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

paulyeo11
Valued Contributor II

Re: How to remove Row with null value ?

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

paulyeo11
Valued Contributor II

Re: How to remove Row with null value ?

Hi Khishboo

I think your solution is more correct . Appreciated.

Paul

Re: How to remove Row with null value ?

Hello Paul,

Why do you want to keep 1 in expression.?

paulyeo11
Valued Contributor II

Re: How to remove Row with null value ?

Hi Tamil

I need to count how many company sales up ?

Paul

paulyeo11
Valued Contributor II

Re: How to remove Row with null value ?

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 ?

Paul,

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

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

Community Browser