Handling null in numeric fields or calculations

Whether in the QlikView script or in an expression, we often have to check if a value is null (absence of value) or blank (empty string) before using it in a calculation. SQL developers will be used to the ISNULL or COALESCE function and it is useful to have an equivalent. There is an IsNull() function in QlikView, which returns a Boolean true/false. My experience is that this does not always work reliably on all platforms and, if I want a IsNull Boolean, I tend to check Len(Field)=0 or Len(Trim(Field))=0 instead. This is a little clunky to use with an If statement in an expression so I find the Alt function to be much more elegant.

Alt can take any number of parameters and will return the first one in the list that is a number. In this case, I will only use two parameters—the field to be tested and then either 0 or 1—depending on what I want the default to be.

Getting ready

Load the following script:

Alt_Example:
Load * Inline [
	Month, LocalSales...