The Coalesce function is very useful for dealing with and handling NULL values in new field formulas. The function returns the first value from the list that is not null, or if all values in the list are NULL, it will return NULL.
Example 1: Suppose you want to create a new field "c" as the sum of two other fields "a" and "b". In the New Field definition area, you would enter the formula
[a] + [b]
In LityxIQ, the result for c for any record in the dataset will be NULL if either a or b is NULL for that record. This may not be what you intend for the result. If you want NULLs to be treated as 0 in this summation, you could instead use the formula:
coalesce([a], 0) + coalesce([b], 0)
This will result in the following: if the variable a is NULL for a record, the summation will use the value 0 instead. Otherwise, it will use the actual value of a. Same for b. The effect is that any NULL value in either variable a or b will be replaced with 0. This will in turn ensure that the result of the summation will never be a NULL value result.
Example 2: Suppose you have appended three incoming datasets together as part of creating a derived dataset. Each incoming dataset has a variable representing zip code, but the variable is named differently in each incoming dataset. In one, it is named "zip", in the second it is named "zipcode", and in the third it is named "Zip Code". However, for this derived dataset, you want to create a single consistent variable named "Final Zip" whose value is whichever of these other zip code fields is populated for the record. The new field definition to enter is:
COALESCE([zip],[zipcode],[Zip Code])