3 Replies Latest reply: May 15, 2014 10:11 AM by MonUserCJ _ RSS

    Behavior of null values in filters

    MonUserCJ _

      Hey all,

       

      Monarch recently behaved in a way that surprised me, and I wanted to post to make sure that my understanding of the issue would be strong enough to avoid mishaps in the future.

       

      I have a model with an external lookup that maps on a character field to bring in a numerical field. Not all records in the original report map, so the value for this numerical field is sometimes (null).

       

      I wanted to filter on this numerical field based on a not equal to--if the field <> 1, include the record.

       

      I guess I thought that null values would satisfy this constraint, but apparently not. I unwittingly excluded a lot of records that I wanted to display.

       

      My question is--is the IsNull function the only way to reliably work with fields that could contain null values? Is it safe to filter based on equality without addressing nulls (e.g., if my filter had been Field = 1, would it have excluded the null values, or included them?)

       

      I would appreciate any insight that anyone can give.

        • Behavior of null values in filters
          Grant Perkins

          Nulls can be a little challenging when considered as 'data'.

           

          In general I would always seek an explicit value or value set to filter with. In this case I think it would probably be using ISNULL to set a flag in a separate calculated field and then filter on that since you know it will have one of two possible values. It also make it easier to check the results against expectations.

           

          HTH.

           

           

          Grant

            • Behavior of null values in filters
              Olly Bond

              Hello CJ,

               

              Monarch can be a little fiddly with nulls - for example adding "abc" and a null string results in a null string. When you work with database sources, there's an option to replace null values with defaults (0,"",01/01/1970), but I frequently still find myself defining calculated fields like if(isnull()=1;0;[field]).

               

              With filters, it might be helpful to think of an expression having three answers, not two. a=b can be either true (1) if both a and b are non-null and are equal, or false (0) if they are both non-null but unequal, or null (1/0) if one or both of them is null.

               

              So <>1 can be true if field is non-null and not equal to 1, false if non-null and equal to 1, or null if field is null. The filter only returns the true matches, so in your case you'd need to include the nulls with:

               

              <>1 .or. isnull()=1

               

              A filter of =1 would exclude the null records.

               

              Best wishes,

               

              Olly