NULL values In QlikView

Handling NULL values In QlikView

NULL is not a value. It is a lack of value. It is a placeholder that marks nothingness. NULLs have certain basic properties: All QlikView fields and all data types in a SQL database are NULL-able.

• In SQL, NULL does not have a data type. In QlikView, this corresponds to the fact that NULLs are not dual, i.e. they do not have both a string representation and a numeric representation.
• NULLs propagate. If you use a NULL in an expression, it will not cause an error. Rather, it will propagate through the expression and yield a result which often – but not always – isNULL.
• NULLs cannot be used as key value to join or link tables.
• NULLs are neither visible nor clickable in QlikView list boxes, unless you make them visible and clickable using a method described below. This means that NULLs are not selectable in QlikView.

Different Types of NULLs: Numerical zero

If a field has a numerical zero in it – 0 – then this of course represents a numerical nothingness, but it is certainly not the same as NULL. The field has a value and is hence not NULL. The IsNull() function will return FALSE and the record will be included in the calculation of both Avg() and Count().

Empty strings and white space

A string that looks empty is another candidate for nothingness, but also this is different from NULL. It could be an empty string, i.e. a string with the length zero, or it could be a string containing white space. In any case, such a string is a string and not a NULL. For these, the IsNull() function will return FALSE and the record will be included in the calculation of Count() but not in Sum() and Avg() since it is not numeric. Note that there are several type of characters for white space: soft blanks (Chr(32)), hard blanks (Chr(160)), the tab character (Chr(09)) and ideographic space (#3000/Chr(12288)). If you use the trim functions, you should be aware that these only remove soft blanks and no other characters.

True NULLs

True NULLs are cases when an existing record in the data model has a field where the value is missing and it hence is marked as NULL. These NULLs have all the properties described in the introduction.

There are several ways that true NULLs can get into the QlikView data model: Databases often contain NULLs and if you use ODBC or OLE DB to load data into QlikView, you will most likely get NULLs in your application. If you have joins or concatenations in the QlikView script, the missing values will be converted to NULLs. This is true both for the external SQL commands (“JOIN” and “UNION”) and for the internal QlikView prefixes (“Join” and “Concatenate”). Some functions, e.g. an If() with only two parameters, Only(), and most notably Null(), may return NULL and if these functions are used in the QlikView script, you will most likely get NULLs in your application. A text file cannot contain any NULLs in itself, so unless you load it using functions that can generate NULLs, or the table is part of a concatenate or a join operation, QlikView will not have any NULLs in the table. The IsNull() function will return TRUE for NULLs and the record will not be included in the calculation of any aggregation function, except Null Count(). How Does Display NULL? List boxes In the picture below, you have a selection of two customers that haven’t placed any orders, i.e. they exist in the Customers table but there are no corresponding records in the Orders table. As a result, all values in the OrderID list box are gray. In other words; for a list box, it is simple: NULL is not visible as an explicit list box entry. If all entries are marked as gray, then the empty set is the answer to the click, i.e. NULL is the answer. If you have a list box where you have a visible entry that is blank; that you can click on; that can be selected, then it is not a NULL. NULLs are never visible in list boxes and can never be selected. Instead, you have an empty string or some kind of white space. Table boxes In a table box, as well as in all other places where a NULL can occur, e.g. labels, text boxes, buttons, etc., NULLs are displayed as dashes. In these places, NULL is visible but not selectable. Chart dimensions For a chart, it becomes more complicated. First of all, a NULL can occur either as a dimensional value or in the measure - the expression. These are two very different cases and should not be confused. Further, the two cases are managed in two different places in the chart properties. With the above data, it would be reasonable to make a chart that shows sales per customer. If there are orders that are not attributed to any customer, then you will get a NULL in the dimension of the chart – a NULL which is displayed as a dash. Below you can see that order nr 10874 has no customer associated: If you don’t want to show lines with NULLs in the dimension, you can suppress these on the Dimensions tab in the chart properties. Chart Expressions: If your data is the other way around, i.e. you have customers that are not attributed to any orders, you will for these get NULLs in OrderID and Amount. However, the Sum() and Count() functions still return zero, which is correct, since Sum(NULL) is zero. Other aggregation functions such as Only() may return NULL, though, and this will be displayed as a dash. If you don’t want to show lines with zeros or NULLs as expression value, you can suppress these on the Presentations tab in the chart properties. So, the bottom line is that NULLs are sometimes visible, sometimes not. Sometimes they are displayed as dashes, and when aggregated, usually as zeros. But they are never selectable.