empty cells not recognised as null

[#1]

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

[robert_mika]

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

Here is article how QLikview handles NULL

NULL – The Invisible Nothing

[#3]

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

[Anonymous]

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]

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

[Anonymous]

Hi,

In script add this,

NULLASVALUE fieldname;
Set NullValue = 0;

Empty cells it will show 0

[#7]

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]

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]

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