Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
robert_mika
Master III
Master III

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

Here is article how QLikview handles NULL

NULL – The Invisible Nothing

Not applicable
Author

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

Anonymous
Not applicable
Author

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


jonathandienst
Partner - Champion III
Partner - Champion III

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
Anonymous
Not applicable
Author

Hi,

In script add this,

NULLASVALUE fieldname;
Set NullValue = 0;


Empty cells it will show 0

Not applicable
Author

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.


MarcoWedel

another solution could be:

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

from yoursource;

hope this helps

regards

Marco

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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