topic Re: Omitting Zero values for counting in QlikView

Omitting Zero values for counting

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

Dear Friends

It is 10 the count with zeros. But I need to avoid zeros & the out put needs to be 6 which is the count after zeros are excluded. The formula used with where clause seems to be not working. Could you please see where the error is?

Thnaks

Neville

Re: Omitting Zero values for counting

Taoufiq_Zarra — Sat, 27 Jun 2020 22:28:50 GMT

Hi Neville,

whene you use len(field)>0 it's particularly when its null,

so in your case :

Data: LOAD * INLINE [ BRANCH, PAID RA, 0 RA, 100 RA, 200 RA, 1000 RA, 2000 RA, 2500 RA, 4500 ]where Fabs(PAID)>0;

output :

Re: Omitting Zero values for counting

nevilledhamsiri — Sun, 28 Jun 2020 13:39:38 GMT

Hi Taoufiq,

Very happy that you provide us quick answers. You simply doing well. The answer for my issue is resolved. What Fabs does in this respect which is something we use to handle growth related issues where two figures are matched related two two different periods.

Thanks you very much

Neville

Re: Omitting Zero values for counting

Taoufiq_Zarra — Sun, 28 Jun 2020 14:07:10 GMT

Hi @nevilledhamsiri

You can simply use :

Data:

LOAD * INLINE [
BRANCH, PAID
RA, 0
RA, 100
RA, 200
RA, 1000
RA, 2000
RA, 2500
RA, 4500
]where PAID<>0;

but I prefer absolute values Fabs