1 Reply Latest reply: Jul 7, 2011 7:02 PM by John Witherspoon RSS

    nullcounts of all fields

    Paulo Hersan

      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
          John Witherspoon

          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))]))