Data Profile Tool

I’ve written in this blog before about how I’m often called on to validate a new data file from a client or to examine a new data extract. My first step is to load data into a copy of Steve Dark’s Data Profiler (Steve's original post is useful - search on quickintelligence data profiler). The data profiler gives me information about exactly what values exist in each of the fields.

I’ve made a few modifications to Steve’s original document:

• Added a table box that adjusts itself to the fieldnames in the file
• Added a small macro that updates the Statistics Box with the fieldname automatically as different fieldnames are selected. A trigger detects the change and automatically configures the Statistics Box
• Added a tab to help search for duplicate values in either a field or combination of fields
• A check in the loadscript to see if the file exists and if it does not exist then it opens a msgbox from the loadscript using a function

You can download an example copy of the file data profiler that I use by clicking HERE

.

I also keep a version of the data profiler that is tailored for loading data from an Oracle table and a version of the profiler tailored for loading data from a qvd file. I think I use the data profiler tools almost every day to examine data in files and tables to help with QlikView document design and other reporting requirements.

These two examples have no data loaded - you will have to do a reload yourself from your own data:

• You can download an example copy of the profiler I use for qvd files by clicking HERE
• You can download an example copy of the profiler I use for Oracle tables by clicking HERE

  ★★★

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.  

  ★★★

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

  ★★★