Coalesce Function

When appending multiple datasets into a single large dataset, there may be a situation where each individual dataset contains a slight variation on a single field.  For example: a field containing zip codes carries the similar names of “zip”, “zipcode”, and “Zipcode” in the various incoming datasets.  The append operation will result in three different variables in the new dataset. This may cause many records to having missing values for those fields not in their original dataset (this is standard operation for appending datasets together).

 

After the append has completed, a single field for zip code can be created.  This field can be populated with the value from either “zip” “zipcode” or “Zipcode”, whichever is populated for a particular record.

 

In order to achieve this, the following formula should be used:

 

FinalZip = COALESCE([zip],[zipcode],[Zipcode])

 

Coalesce returns the first value from the list that is not null, or NULL if they are all null.  This is simpler than using a series of CASE/WHEN.  After this new field has been created, the now obsolete zip codes can be dropped.