Finding NULL

NULL is not a value. It is a lack of value. It is a placeholder that marks nothingness.

So how do you search for NULLs? How do you find the customers that didn't buy product X? Or, how do you find the users that didn't log on this month? There is no search string that matches NULL and even if there were, you can’t select NULL.

NULLs cannot be selected explicitly, so to find the records with NULLs, the selection must always be made in another field. In the example of customers not having bought product X, it means that the Product field for some customers is NULL. Hence, you need to select the customers for which the Product is NULL.

In other words – you need to make the selection in a field other than where you have the NULL. And here’s how you do it:

    Set your selection criteria the normal way.
    Use Select Excluded on the field where you want to negate the selection

For example, if you want to find customers that have not bought Basket Shoes, then you should first select Basket Shoes from the Product list box. Then you will in your Customer list box have the customers that indeed bought Basket Shoes. But the grey customers are the ones you are looking for. So, right click, and Select Excluded. Voilà!

to find users that have not logged this month. Analogously, you first select the month and then you negate the selection by using Select Excluded on the User list box.

A third example could be that you want to find the customers that have not bought any product at all. Then you should first right-click the products and Select All. This will maybe not change very much, but it will exclude the customers that never placed any orders. In other words: These are now gray and can be selected using Select Excluded.

A final example could be that you have a combination of criteria, e.g. you want to find customers that have not bought any shoes in the last few months. The method is still the same: Select relevant products and select relevant time range. The possible customers are the ones that have bought of the products in the time range, and the excluded customers are the interesting ones. Select Excluded!

 
However, when you have a combination of selections, QlikView doesn’t always remove both of the initial selections when you select the excluded values, so to get it right you should combine it with a Clear Other Fields. A good, user-friendly solution is to put both commands in a button that you label Select Excluded Customers.

If you want to read more about how to manage NULLs in your QlikView application,

Creating a Trial Balance Sheet in QlikView

When using QlikView, sometimes we are faced with challenges such as how do we transform an Excel spreadsheet that looks like the image below into a trial balance chart that allows us to see the monthly activity for each company and account.

Well using various functions and features of QlikView such as:

•          CrossTable Load of Excel spreadsheet

•          IsNull() function

•          RowNo() function

•          Peek() function

•          Date functions

•          Preceding load

We can create a trial balance sheet that looks like this:

Creating this chart involves loading the Excel spreadsheet into QlikView in a format that works best for us using a CrossTable load.  After sorting this data by Company Number, Account Number and Month Year, we are ready to create the Opening and Closing fields that we will need for the trial balance chart.  Using the RowNo() and Peek() functions, we are able to create the opening and closing balance fields for each account number on a monthly basis allowing the user to see what the activity was like during any given month.  Here is a snippet of what that script looks like:

Fiscal year

A common situation in Business Intelligence is that an organization uses a financial year (fiscal year) different from the calendar year. Which fiscal year to use, varies between businesses and countries. 

A fiscal year other than the calendar year implies a number of additional requirements in the QlikView app: The most obvious is that the year used in all charts and reports must correspond to the fiscal year which runs over a different set of dates than the calendar year.

Further, the notation sometimes changes: You probably want to display years as ‘2012/2013’ instead of just the year number.

Also, other fields, e.g. Month and Week must be assigned to the fiscal year as well as the calendar year.

Finally, the sort order of field values changes in some cases. E.g. you want to sort the months using the first financial month as the first month: [Apr..Mar] instead of [Jan..Dec]. (Compare with the month order in the graph and the list box.)

There is a very simple way to achieve this in QlikView:  Just add the necessary fields in the master calendar and use these in all situations where you need a calendar field. There are many ways that this can be done, but my suggested solution is the following:

1. Create a variable that contains the month number of the first month of the fiscal year. Assuming that April is the first month of your fiscal year, this variable should get the value ‘4’.
2. Create numeric values of the necessary fields. Usually the number of the fiscal year is defined by its end, so (again using April as the first month) April 2013 belongs to the fiscal year ‘2014’.
3. Create dual values of the necessary fields.

The script for creating fiscal year and fiscal month then becomes:

Set vFM = 4 ;                                                          // First month of fiscal year

Calendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year         Dual(Month, fMonth)                as FMonth,           // Dual fiscal month
          *;
Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year
         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month
          *;
Load Year(Date)                              as Year,           // Your standard master calendar
         Month(Date)                            as Month,
        …

Other fields, like week, day, etc. can also be created in a similar way.

A comment on the field naming: In this script I use lowercase ‘f’ as prefix for the numeric values and uppercase ‘F’ for the dual values. In real life you may want to just have the dual fields (no numeric duplicates) and name these differently, e.g. just ‘Year’ and ‘Month’. If you do, you must also rename the original calendar year and calendar month accordingly.

The bottom line is anyway that you can solve this problem just by adding a couple of lines in you master calendar. No set analysis is needed. And no complex chart expressions are needed.

Loading Images into QlikView

As a QlikView developer I am often asked to load images into QlikView. In some instances the images are associated to other data fields and in other instances they are to be loaded in to the application to help convey a message. For example, let’s say that you need to bring in flags of countries that are to display when the corresponding country is selected and you need to display an icon that indicates whether sales for a country are above or below a predefined threshold. It sounds like an easy enough task; so how do you do it?

Well, QlikView offers the developer the Bundle function that can be added to the Load statement. Bundle Load statement allows the developer to load the image files directly into the QlikView application for portability.

The Bundle Process is a very simple scripting process. The syntax for the Bundle Load should look like this:

The image file should contain two fields:

• The first field is an identifier such as an index number, image name, a key field that associates the image back to other data files, etc.
• The second field should contain the path to the image.

This file contains the CountryID so that I can associate the flags with the countries

This file contains just an image name because I am just using these icons as a reference and they are not associated to any fields in other files.

Once the images are loaded into QlikView we can now reference them using the INFO() function and a standard IF Statement:

I am using the INFO() function to display the flags so whenever a Country is Selected, QlikView will know to go grab the corresponding image based on the CountryID. If we needed to, we could do the same thing with the symbols by simply adding a list box for Image Name and selecting an image from there. For this example though, I am using a standard IF Statement to display the symbol for the country sales.

Here is what it looks like when the user selects a country. The Info() function displays the image associated with CountryID =1 and because Sales were above the threshold the green square is displayed.

One note of caution, when images are loaded into a QlikView application using the Bundle Load statement, both the amount of RAM and the size of the application increase so consider both the size and the amount of image files before deciding to use the Bundle LOAD statement.

Slowly Changing Dimensions

As one creates QlikView applications one sometimes encounters a data modeling problem where a dimensional attribute varies over time. It could be that a salesperson changes department or a product is reclassified to belong to another class of products.

This problem is called Slowly Changing Dimensions and is a challenge for any Business Intelligence tool. Creating an application with static dimensions is simple enough, but when a salesperson is transferred from one department to another, you will have to ask yourself how you want this change to be reflected in your application. Should you use the current department for all transactions? Or should you try to attribute each transaction to the proper department?

First of all, a changed attribute must be recorded in a way that the historical information is preserved. If the old value is overwritten by the new attribute value, there is of course nothing QlikView can do to save the situation:

In such a case, the new attribute value will be used also for the old transactions and sales numbers will in some cases be attributed to the wrong department.

However, if the changes have been recorded in a way so that historical data persists, then QlikView can show the changes very well. Normally, historical data are stored by adding a new record in the database for each new situation, with a change date that defines the beginning of the validity period.

In the salesperson example, you may in such a case have four tables that need to be linked correctly: A transaction table, a dynamic salesperson dimension with the intervals and the corresponding departments, a static salesperson dimension and a department dimension. To link these tables, you need to match the transaction date against the intervals defined in the dynamic salesperson dimension.

This is an intervalmatch. The solution is to create a bridge table between the transaction table and the dimension tables. And it should be the only link between them. This means that the link from the transaction table to the bridge table should be a composite key consisting of the salesperson ID (in the picture called SPID) and the transaction date.

It also means that the next link, the one from the bridge table to the dimension tables, should be a key that points to a specific salesperson interval, e.g. a composite key consisting of the salesperson ID and the beginning and end of the interval. Finally, the salesperson ID should only exist in the dimension tables and must hence be removed from the transaction table.

In most cases of slowly changing dimensions, a salesperson (or product, customer, etc.) can only belong toone department (or product group, region, etc.) at a time. In other words, the relationship between salesperson and interval is a many-to-one relationship. If so, you can store the interval key directly in the transaction table to simplify the data model, e.g. by joining the bridge table onto the transaction table.