Finding NULL

[hic]

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:

1. Set your selection criteria the normal way.
2. 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à!

The second example was how 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, you should read this Technical Brief.

HIC

[#2]

Henric,

why didn't QlikTech make NULLs searchable? In QVD files NULLs have own code like any other field dictionary symbol and therefore are perfectly searchable. I assume that internal data representation in QllikView mirrors QVD so NULLs should be searchable in QlikView apps too. I'm pretty sure NULLs are not excluded from the bit-stuffed index.

PS. Life would be a bit easier for developers if they could write something like

Count( {< Orders=null() >} Customers)

or have an option of explicitly selecting NULL in a listbox

PPS. Excellent whitepaper, btw

[hic]

On one hand, you have a point: True NULLs could be stored in the symbol table so they could in principle be made visible and searchable.

But on the other hand...

• From a principal point, NULLs should not be treated as values. They should be hidden. They are not values.
• It wouldn't solve the problem, since Missing values cannot be stored in the symbol tables. And missing values are really the most common type of NULL. So if true NULLs would be visible, but missing values not, I think we would have an even more confusing situation...

HIC

Explanation:

True NULL = The record exists, but the field is marked as lacking value.

Missing value = The record is missing, so there is no cell that can be marked NULL. What you have in the order table for customers that have not placed orders.

[#4]

I see your point. Yes, I agree -- it can be even more confusing, indeed.

[rbecher]

But NULLs are not really stored in the symbol tables. The pointer in the record is just negative and so it points to nowhere.

[hic]

The discussion is really hypothetical - could we change QlikView so that NULLs are searchable and selectable? The answer is probably yes. But such a solution would never cover the missing values, and then the solution isn't interesting.

HIC

[#7]

Hi Henric,

  How the load command script function NULLASVALUE change this rule ?

[hic]

The NullAsValue statement will replace all True Nulls with the string stored in the variable NullValue. So these NULLs will become visible, searchable and selectable.

But it will not affect Missing Values, so in most cases it will not be so useful.

HIC

[Or]

Here's a case I ran into today..

I am loading a table with a structure of:

Cost Center - Month - Hours

This table is left-kept so I only get results for the relevant Cost Centers (I could probably write the list into the original SQL code, but left-keep is easier). However, due to human error, it turns out that the list of relevant cost centers had some centers listed twice, once properly and once with no details (just the number without the name):

Cost Center - Cost Center Name

1 - A

2 - B

2 -

The resulting data model was that I got the correct hours for every cost center, and a certain amount of hours not associated with any Cost Center Name (in the example above, associated with Cost Center 2). Because these hours were duplicates of correct values (i.e. they are associated with the correct Cost Center line in addition to the extra line), I couldn't figure out any way to guess where they were coming from. I eventually found the problem lines in the Excel file used as a list of relevant Cost Centers (it was buried several screens below the actual list, after a couple hundred blank lines), but I'd love to hear if there's an easy way to uncover this sort of null behavior.

[prieper]

Think that the OTHERSYMBOL-function is quite useful in detecting and filtering missing mapping-values:

SET OTHERSYMBOL = +;

Data: LOAD CustomerCode, Order, ... FROM ...;

Customers: LEFT KEEP (Data) LOAD * INLINE [CustomerCode, Customer

A, AAAAA

B, BBBBB

+, #N/A];

In case that there are some entries in the maintable, for which there is no customer-name mapped, they may be filtered by selecting #N/A.

HTH

Peter

[hic]

Depends on what you want to do... If you want to hide duplicates, you should probably clean up the dimension table. Something like

Load [Cost Center], [Cost Center Name] From CostCenters

          Where Len(Trim([Cost Center Name]))>0 and not Exists([Cost Center]);

Load [Cost Center], [Cost Center Name] From CostCenters

          Where not Exists([Cost Center]) ;

This loads only one record per cost center.

But if you instead want to make duplicates visible (so you can clean the DB), then you could do something like

CostCenters:

Load [Cost Center],

          If(Len(Trim([Cost Center Name]))>0, [Cost Center Name],

  'Missing name') as  [Cost Center Name]

          FromCostCenters ;

CostCenterCount:

Load [Cost Center],

          Count([Cost Center]) as CostCenterCount

          From CostCenters

     Group By [Cost Center];

This allows you to look for duplicates as well as for missing names.

HIC