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.
★★★
4 comments:
Sadly, alt only works with numbers and there is no equivalent function for strings. For strings, you have to rely on a null or Len test.
I use the alt function and like Rob said, wish there was an all-encompassing function like NVL. My real question is... what is the signifigance of the picture?
@Aaron Is a missing tooth null?
Hi Aaron,
It's human to see patterns and connections in natural (random) things. Similar to many visualizations, the pictures in the blog are mostly for visual interest. They also help you remember by stamping an image in your brain as you read the material.
(Karl, its more like an empty string)
Post a Comment