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.  


  ★★★

Wednesday, April 5, 2017

Converting Edited Number Text into a Numeric Field

My co-worker, Naveen, had a requirement for a document that would load some financial data from a file that a client had sent to us. The client had created the file with a “screen scraping” type of application that captured the number fields as edited numeric text. For example, a field might contain  ($3,046.10)   So, that example includes a dollar sign, comma as a thousands separator, period as a decimal point, and parenthesis to indicate a negative number or credit.

Naveen needed to load the data as a number. Here’s how it was done:
We used two functions. A Num# function converted the text string into a dual field containing both a text portion and a numeric portion. Then, an outer Num function extracted just the numeric portion. For example, one of the number fields was named AUG. In the loadscript, the line that converted the edited number into a simple numeric field looked like this:
Num(Num#(AUG, '$#,##0;($#,##0)'))

The edit string or format code, the part within the single quotes, can vary depending on your requirement. The edit string I used in the example is a good one for the kind of editing you might find in a financial spreadsheet.

Here's a few other examples of edited number text and how it looks after using this expression:
AUG
Num(Num#(AUGUST,'$#,##0;($#,##0)'))
$0
0
$1
1
$1,251
1251
$1,465
1465
$1,466
1466
$3,724
3724
$4,158
4158
($11)
-11
($132)
-132
($2)
-2
($4)
-4
What now?
         (this is a null)
19
19
25.12
25.12
25.1234
25.1234
25.3
25.3
2,070
2070


  ★★★