Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

empty cells not recognised as null

Hi there!

I'm a newbie in qlikview and have been encountering issues on loading excel datasets extracted from a software (using SQL). As I'm checking completeness of data, null values (ie., empty cell with absolutely no characters) are what I'm really after. However, empty cells are not recognised as null by qlikview. I'm trying to avoid cleaning up the dataset first before loading to qlikview due to huge amount of data.

Thanks for your valuable input.

Regards

nyarky

18 Replies
Highlighted

Re: empty cells not recognised as null

Can you describe what you are doing to check your data set?

Here is article how QLikview handles NULL

NULL – The Invisible Nothing

Highlighted
Not applicable

Re: empty cells not recognised as null

ya, check with isnull() or len(field)>0

Highlighted
Creator II
Creator II

Re: empty cells not recognised as null

Hi Nyarky,

Is this Null value accountable in your report? If so convert the Null as zero and load the data. You can convert the Null as zero in load script itself. Please refer the below link for this activity

Substitute Zero for NULL Value

Regards,

Nandha


Highlighted
MVP
MVP

Re: empty cells not recognised as null

And a detailed white paper can be found here: NULL handling in QlikView

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Specialist II
Specialist II

Re: empty cells not recognised as null

Hi,

In script add this,

NULLASVALUE fieldname;
Set NullValue = 0;


Empty cells it will show 0

Highlighted
Not applicable

Re: empty cells not recognised as null

Hello

Thank you all for your input.

@Robert Mika: As I am checking for the completeness of my data prior to quality check, I notice that there are data fields that appear 100% complete even if i know they there are empty cells (null data).

I'm afraid I can use zero as i have fields that have zero as a category or type.

thanks again for the valuable comment.


Highlighted

Re: empty cells not recognised as null

another solution could be:

load if(len(yourfield),yourfield) as yourfield

from yoursource;

hope this helps

regards

Marco

Highlighted

Re: empty cells not recognised as null

You should know that not many products are able to indicate a real NULL value to an end-user. For example, most DBMS (database management systems) have knowledge of the concept NULL, but Excel for example does not. Exporting your date from a DB through Excel will convert real NULL values into empty strings, which are valid values (meaning that cells in this export file may be empty but empty in this case is a valid value different from NULL).

If you want to verify your data, you should determine what is the best practive to peek into your Database: connect QlikView straight to the database itself through an ODBC connection or even better: OLEDB (both products know what NULL is) or transform your data by way of an export file format (Excel, of all file formats that were ever invented ) and almost loose control of what is present in your DB and what is not.

If Excel won't go away, you should decide on a different set of values that indicate a "no valid value present"- or "this value has not been initialised before"-state. Is the empty string enough for you, then forget about NULL in your input stream and test every value with len(trim(FieldName)) > 0, as has been suggested before. Code like that will discard blank strings as well.

Peter

Highlighted

Re: empty cells not recognised as null

BTW the same test lets you restore NULL values in QlikView, although they were "lost in translation". See Marco's example below, which essentially comes down to:

LOAD if (len(yourfield) > 0, yourfield, NULL()) as yourfield

FROM yoursource;

(pieces in italics are the defaults; whether you add them or not, the end-result is the same).

Peter