Sunday, May 14, 2017

Have you got a function similar to SQL NVL ?


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:

Rob Wunderlich said...

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.

Unknown said...

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?

Karl Pover said...

@Aaron Is a missing tooth null?

-TB said...

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)