Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

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à!

 

Customers.png

 

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!

 

Shoes.png

 

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.

 

Button.png

 

If you want to read more about how to manage NULLs in your QlikView application, you should read this Technical Brief.

 

HIC

18 Comments
Not applicable

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

3,354 Views

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.

3,354 Views
Not applicable

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

0 Likes
3,354 Views
MVP & Luminary
MVP & Luminary

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

0 Likes
3,354 Views

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

3,354 Views
Not applicable

Hi Henric,

  How the load command script function NULLASVALUE change this rule ?

0 Likes
3,354 Views

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

0 Likes
3,354 Views
Or
Valued Contributor II

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.

0 Likes
3,354 Views
prieper
Honored Contributor II

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

3,354 Views

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

0 Likes
3,354 Views

You are absolutely right that OtherSymbol sometimes is useful. When you run a script that has OtherSymbol in the data, the symbol is replaced by one or several values that exist in the same field in previously loaded tables, but have not yet been loaded from the current table. Hence, it can be used to mark missing values. However, it has some peculiarities…

  1. You need to load your tables in the right order. In your example, loading Customers before you load Data will not yield the same result as the opposite.
  2. You usually want to have the OtherSymbol as the last record of the table. Having it in the middle of the table or in the beginning will not yield the same result as in the end.
  3. The OtherSymbol changes the number of records. If you e.g. use the OtherSymbol in the CustomerID in the Orders table, this table will have records for customers that never placed any orders. In other words, Count(OrderID) may return an incorrect answer.

But if you are aware of these limitations, the OtherSymbol can be very useful.

HIC

0 Likes
3,354 Views
robert99
Valued Contributor III

Thanks for this post. But I hope that QV eventually make nulls selectable in some situation

I initially preferred to not join tables. I believed this would keep the file size smaller and keep a clearer record of the table structure in the table viewer.

But this null issue required me to join some (non data / dimension) tables (I used mapping tables to do this but would have preferred not to). I felt this was one area where QlikView could be improved so that tables do not need to be joined to allow selection of Nulls

Example. I had a number of situations like this

InvNum       VALUE          SalesManID

123               1,000            990

124                2,000           991

125                3,000          No number entered

edit ok This above example can use set null display = '<Null>' (or set nullvalue). And mapping tables were used for tables joined to the above table where the SalesManID was missing from the joined table. So this is a type 1 missing value not a null 

                   

So invoice 125 would not have a Cust name if I reported by Cust Name and Sales (invoice) value

I was able to improve the pivot table (chart) as follow

Dimension formula

=if (len (Trim (SalesManID)) >0 , SalesManID , 'Other')

But I could not select / search / filter by Other (until I went the mapping table route)

What I would have liked is to have an option under Dimensions

Say under suppress when value is Null

Another option

Show when value is Null as name _______________

This would allow a name to be typed in (say Other or missing) and would also make Other selectable

I know there are other ways to achieve this but it mainly involves joining tables (as far as I can see) Exists does work but creates another field to drill down (which is confusing IMO) but only works (I think) for the first table anyway (ie if there are say more than one concatenated sales tables)

0 Likes
3,354 Views

I agree that it (hypothetically) would have been nice to have NULLs and Missing values selectable. But it is not so easy...

Making them selectable would imply changes in the internal data structure - changes that at best only would slow the evaluation down, and at worst would be impossible to implement. Examples:

1) NULL and Missing value are not the same - they would need to handled differently. See more on NULL handling in QlikView for the differences. True NULLs would in principle be simple to make selectable, but not Missing values.

2) It would affect all counting functions: NULLs and Missing values should not be counted - neither in frequency counts, nor in Count().

3) It would affect the Logical inference engine, the engine that determines which values are possible: And what would it mean that "Missing values are possible"? This concept is not defined - not until you define your cube (pivot table) with an aggregation. An example of a definition could be: "Is there a value of the field Amount, for which there is no SalesManID?" Note that if you replace the word "Amount" with "Month" in the previous sentence, you will get a different answer. So, SalesManID sometimes has Missing values, sometimes not.

So, in my view, Missing values are best defined in a cube (when the aggregation function is given), exactly the way you have done it.

HIC

3,354 Views
robert99
Valued Contributor III

Thanks for the reply. QV is a brilliant product but this selectable Null issue bugs me. That's why I went the mapping table route

But the mapping table solution worked very well. Better than the other methods I had used before reading your applymap Blog.

http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

I have also used where not exists a lot as well. Although I usually went where not Exists(field2,field) drop field field2. I possible overused the mapping table. I had about 25 tables and reduced down to 10-15. When we purchased QV the consultant (who checked my work) was anti not only synthetic tables but also lots of tables as well

I would prefer to keep all tables as a separate table (if possible) but his view was it impacted on performance. I'm unsure if it does or not. edit http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/12/to-join-or-not-to-join  I found this article. Thanks. It answers my question.

0 Likes
3,354 Views
Not applicable

This commentary is very useful, however, (and perhaps I missed it) I have need where I want to exclude null values during the LOAD process. I am inheriting an existing script that is loading in about 20 fields from a datasource, where some fields have nulls and some do not. For 1 specific field [Project Status], I want to EXCLUDE the entire record if [Project Status] value is Null.

Note: the only other two possible values are Open and Closed.

This seems basic and i searched the community and couldn't locate the correct syntax.

Any suggestions are appreciated.

0 Likes
3,354 Views
prieper
Honored Contributor II

LOAD * FROM .... WHERE LEN(TRIM([Project Status]));

HTH Peter

3,354 Views

prieper‌'s solution above is what I would use too. However, it's good to know that it will remove not only NULLs, but also empty strings and strings consisting of one or several blanks. But since this is what you usually wants, this is OK.

However, if you only want to remove NULLs, but keep the strings, you should instead use

  Load ... From ... Where not IsNull([Project Status]) ;

HIC

0 Likes
3,354 Views
Not applicable

Thanks Peter, this appears to work the way I had hoped. i will store this syntax for future reference. Many thanks!

0 Likes
3,354 Views