4 Replies Latest reply: May 15, 2014 10:02 AM by dezideroo98 _ RSS

    Blank line

    dezideroo98 _

      Hi there,

       

      I have a report in which some line have data, while others do not. Can I use either the Lookup or Calculated function to specify that if at the specific field location is empty, do not display, or display a 0 value.? I tried looking into the Help section but was unable to get it working properly. The field that I am checking are numeric.

        • Blank line
          Data Kruncher

          Yes, you can use a calculated field to convert blanks, or technically nulls, to a zero value, just like this:

           

          [SIZE=2]if(isnull(Field),0,Field)[/SIZE][/CODE]

            • Blank line
              Olly Bond

              Hello,

               

              You also can set in the Options > Input dialog a default rule to replace null values with defaults depending on the type of data the field contains - e.g. "" for a string, 0 for a numeric field, and 1 Jan 1970 for a date field.

               

              Best wishes,

               

              Olly

                • Blank line
                  Data Kruncher

                  Of course Olly is correct, and using the Input Options is a quick, easy and effective way to [I]display[/I][/B] default values.

                   

                  Just don't try to perform any calculations involving those default values, as the true value hiding behind that default is a null value that will result in an incorrect final calculation value.

                   

                  You may want to get in the habit of using a calculated field to replace the null values, just in case the values do get used in calculations later on, as one sometimes can't predict what will become of a model in the future. An extra minute or two up front might just prevent problems yet to come.