Null Values

Some of my most frequent questions concern null values. How do I count them? How do I show them? The short answer is ‘you don’t’ because you’re trying to show or count nothingness. But there ways around the problem.

Fortunately, Qliktech has finally put out a technical brief on the subject. The first 12 pages should be most useful to the data specialists. MAs will find the last two pages helpful:

NULLs and missing values in Set analysis

In set analysis, searches are made using double quotes. Also advanced searches can be made, and a search for NULLs can be made using the logic described above. Hence:

     Concat({$<PersonID={"=NullCount(phone)>0"}>} distinct PersonID)

evaluated on the data in the example above in the paragraph “Using advanced search” will evaluate to ‘Z’, exactly as in the example in this section, i.e. the people that do not have a phone number.

Empty element sets, either explicitly e.g. <Product = {}> or implicitly by a search with no hits <Product = {"Perpetuum Mobile"}>, mean no product, i.e. they will result in a set of records that are not associated with any product.

Note that the set modifier <Product = > is not the same as <Product = {} >. The former merely removes the existing selection in the field, whereas the latter returns an empty set.

Set operators

In set analysis, set operators can be used to find the complement to a selection, i.e. the excluded records. For example, the set expression

     {$<OrderID={"*"}>}

will pick out all possible OrderIDs – but not the NULLs – and consequently

     {1-$<OrderID={"*"}>}

will return the complement: it will pick out all customers that have an empty OrderID set, i.e. that have not placed any orders.

Implicit field value definitions

Finally, in set analysis it is also possible to use the implicit field value definitions P() and E(), which returns the set of possible values and excluded values, respectively. The E() set function is especially useful. Example:

     {$<Customer = E({1<Product={"*"}>})>}

This expression will pick out the customers that are excluded when all products are selected, i.e. the customers that have not bought anything. Further:

     {$<Customer = E({1<Product={'Shoe'}>})>}

This specific expression will pick out the customers that are excluded when the product “Shoe” is selected, i.e. the customers that have not bought any shoes.