topic Re: nullcounts of all fields in QlikView

nullcounts of all fields

phersan_sme — Thu, 07 Jul 2011 21:29:09 GMT

Hi all,

I was wondering if there was an easy way to leverage the system field, $Field, in order to set up a table that lists the field name and its nullcount

I want the output to be something like:

FieldName NullCount

ID 0

Age 10

Region 3

Name 2

Address 47

Email 40

SSN 28437

I can write script and loop through all the field names and get the nullcounts that way, but I would like to keep the association on the calculation as well. For example, if I pick USA as the region, obviously the NullCount for Region would go down to 0, but I would be able to see the nullcounts of all other fields where Region is USA. Is there a way to do this, without brute force?

Thanks in advance,

Paulo

Re: nullcounts of all fields

johnw — Thu, 07 Jul 2011 22:26:42 GMT

This seems to work with $Field as the dimension. You have to list each table you care about separately since you have a different ID to count for each table. I suspect in your case all the fields you care about are on a single table so you can simplify that part. There's probably a simpler way than this.

pick(match($Table,'MainTable','OtherTable')
,count(ID)
-pick(match($Field,'$(=concat($Field,chr(39)&','&chr(39)))')
$(=concat(',count({<['&$Field&']-={}>} ID)')))
,count(ID2)
-pick(match($Field,'$(=concat($Field,chr(39)&','&chr(39)))')
$(=concat(',count({<['&$Field&']-={}>} ID2)'))))

Edit: OK, here's a little simpler. I still think it should be easier than this.

pick(match($Table,'MainTable','OtherTable'),count(ID),count(ID2))

-pick(match($Field,'$(=concat($Field,chr(39)&','&chr(39),$FieldNo))')

,count([$(=concat($Field,']),count([',$FieldNo))]))