topic How to remove row when column 1 and 2 are null ? in QlikView

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

paulyeo11 — Mon, 19 Sep 2016 08:47:37 GMT

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

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

paulyeo11 — Mon, 19 Sep 2016 08:48:16 GMT

my qvw

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

Anil_Babu_Samineni — Mon, 19 Sep 2016 08:57:51 GMT

What is the use of 1

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

tamilarasu — Mon, 19 Sep 2016 09:06:06 GMT

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 ?

paulyeo11 — Mon, 19 Sep 2016 09:07:44 GMT

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 ?

Anonymous — Mon, 19 Sep 2016 09:24:51 GMT

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 ?

sasikanth — Mon, 19 Sep 2016 09:27:18 GMT

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 .

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

Anil_Babu_Samineni — Mon, 19 Sep 2016 09:33:17 GMT

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 ?

paulyeo11 — Mon, 19 Sep 2016 09:39:30 GMT

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 ?

paulyeo11 — Sun, 25 Sep 2016 12:40:32 GMT

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