9 Replies Latest reply: Sep 25, 2016 8:40 AM by Yeo Poh sai

# How to remove row when column 1 and 2 are null ?

Hi All

I have a Table i want to add one more column 3 , which display number of company.

it should only stop at 11

Can some one share with me how to remove those row after 11 ?

Paul

my qvw

• ###### Re: How to remove row when column 1 and 2 are null ?

What is the use of 1

• ###### Re: How to remove row when column 1 and 2 are null ?

Hi anil

I use 1 for count number of company.

Expression i enter 1 , and i set accumulation.

Paul

• ###### Re: How to remove row when column 1 and 2 are null ?

So, You need to do this

This is for 1 Expression

If( Not IsNull(If(Sum({<SEGMENT -= {'Related Coy'},aging_ar = {'>=181'} >}total_ar  * Pick(Match(US_Cust_AR, 'EUR', 'NT', 'USD', 'YEN', 'BAH', 'SGD'), 1.64, (1/24), 1.25, 0.012, 1/24, 1)) > 0,

Sum({<SEGMENT -= {'Related Coy'},aging_ar = {'>=181'} >}total_ar  * Pick(Match(US_Cust_AR, 'EUR', 'NT', 'USD', 'YEN', 'BAH', 'SGD'), 1.64, (1/24), 1.25, 0.012, 1/24, 1)))) and Not IsNull (If(Sum({<SEGMENT -= {'Related Coy'},aging_ar = {'>=181'} >}total_ar  * Pick(Match(US_Cust_AR, 'EUR', 'NT', 'USD', 'YEN', 'BAH', 'SGD'), 1.64, (1/24), 1.25, 0.012, 1/24, 1)) > 0,

Sum(if(L5M='-1',sales)))),1)

And Then Suppress Zero-Values should check from Presentation Tab.

• ###### Re: How to remove row when column 1 and 2 are null ?

Hi Paul,

Since your second column label is dynamic, you can try like below.

If( Not IsNull(Column(1)) and Not IsNull (Column(2)),1)

Note: File attached.

• ###### Re: How to remove row when column 1 and 2 are null ?

Yes that work perfectly.

Expression 3 as per Tamil's suggestion above

If( Not IsNull(Column(1)) and Not IsNull (Column(2)),1)

• ###### Re: How to remove row when column 1 and 2 are null ?

Hi Tamil & All Other

Thank you very much it work fine now.

Paul

• ###### Re: How to remove row when column 1 and 2 are null ?

Hi All

below is the new post on below link table :-

https://community.qlik.com/message/1130281#1130281

Hope some one can help me.

Paul

• ###### Re: How to remove row when column 1 and 2 are null ?

HI paul ,

try to use Calculated Dimension some thing like

=if(not isnull(aggr(sum(sales), company_ar)),company_ar),

and check the option suppress null values.

one more static option is also available (but not recommend)

Presentation tab--> select the column--> Check the box max number & provide value

i will only work when row number id fixed .