topic Re: count and isnull in QlikView

count and isnull

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

hi guys,
there is null value in So Number Edit
and i have to count So Number Edit

if i use this :

='Total So Edit'&' = '&count(distinct [So Number Edit])
& ' Total Invoice = '&Count(DISTINCT([Total SO No]))

it will get null value,

i want it without null value ..

what should i do??

saar_lorena — Tue, 18 Jun 2019 06:30:16 GMT

Hi,

Try this:

In your script, first you need to check when is null or not.

So:

LOAD *, If(IsNull([So Number Edit]), 'T', 'F') as SoNumberEditIsItNull;

='Total So Edit'&' = '& If([SoNumberEditIsItNull]='T', count(distinct [SoNumberEditIsItNull])

davinfrost — Tue, 18 Jun 2019 07:30:54 GMT

hi , i want to count all status edit, but i dont want to count null data '-'

i want use this on textbox, i used this on chart, but it isnt work on textbox?

=If(len(trim([So Number Edit]))='0','0',count(distinct[So Number Edit]))

tresB — Tue, 18 Jun 2019 07:45:55 GMT

Try using set analysis like:

=count( {<[So Number Edit]={"=len(trim([So Number Edit]))>0 " >} distinct [So Number Edit])

davinfrost — Tue, 18 Jun 2019 08:53:31 GMT

error in set modifier ad hoc element list.

tresB — Tue, 18 Jun 2019 09:08:37 GMT

Yeah, needs a correction. Try:

=count( {<[So Number Edit]={"=len(trim([So Number Edit]))>0 "} >} distinct [So Number Edit])