Handling NULL Values

Identifying null values can be an important part of any analysis of property data. A lot of property data is sourced from local governments. In the absence of national standards, each local government not only makes determinations about what data it collects, but also how it chooses to represent missing data.

Numbers

Missing numeric data is usually referenced in a SQL database with either a null value or a zero. In some cases, missing numeric data might be represented by a negative number such as -1. There will often be situations in which zero and negative numbers have a valid meaning. e.g. 

Strings

Missing string data is usually referenced in a SQL database with either a null value or an empty string.

Dates

It is more common for missing dates to be represented as nulls. In some cases, dates may be stored as numeric values which count the number of days since a base date known as an epoch - e.g. 1 Jan. 1900.  If it is considered more likely that a property with a missing date is stored as 1 Jan 1900 rather than that date being the actual value then it may be necessary to filter out those dates.

Example

The following example shows part of a query designed to generate a field population report for Foreclosure data. In order to establish the number of non-null values exist for each field we must transform the data to ignore values that should be considered null

Count(NullIf(AuctionAddress,'')) As AuctionAddress,
Count(LoanMaturityDate) As LoanMaturityDate ,
Count(CASE When OriginalLoanAmount <= 0 Then Null Else OriginalLoanAmount END) As OriginalLoanAmount ,