I work with a
group of people who are all experts in SQL. They all also have varying levels
of technical skills and QlikView skills. One question I get frequently is
whether QlikView has a function like the SQL

*NVL*function.
For those of
you not familiar with relational database SQL language, the

*NVL*function takes two arguments: a field name or expression and a default value to be returned if the first argument is null. For example, a SQL database query may include the function like this:
NVL(ORD_DISCOUNT, 0) AS ORD_DISCOUNT

And that would
tell the SQL processor to look at the value of ORD_DISCOUNT and if it is null
then return 0 as the function value otherwise return ORD_DISCOUNT.

When people ask
about achieving the same thing with QlikView, I usually start by telling them
that they can code an “if” statement like this:

If(IsNull(ORD_DISCOUNT),
0, ORD_DISCOUNT)

and then I explain that there is a
built-in QlikView function that can be used similar to NVL as long as the field you are checking is supposed to be numeric. It is the

*Alt*function.
The Alt
function accepts any number of arguments and looks at each of them going from
left to right and returns whichever one is a valid number. So, repeating our
example, if ORD_DISCOUNT is null then the following function will return a
zero but if ORD_DISCOUNT is a valid number then the function returns ORD_DISCOUNT:

Alt(ORD_DISCOUNT,
0)

The Alt
function will treat the last or rightmost argument as an “else” condition and return
that value if none of the preceding arguments are numeric. The rightmost value
need not be numeric so you could code something like this:

Alt(ORD_DISCOUNT,
'Discount is missing')

The Alt function
may be used in the loadscript code or in chart expressions.

If you are
interested, the QlikView Help (search in Help for

*Conditional Functions*) shows an interesting example for how the Alt function can be used to identify a date when the date value may be any one of several different date formats.
★★★