topic Re: empty cells not recognised as null in QlikView

empty cells not recognised as null

Fri, 20 Nov 2020 18:02:30 GMT

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

Re: empty cells not recognised as null

robert_mika — Fri, 03 Jul 2015 05:31:57 GMT

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

Here is article how QLikview handles NULL

NULL – The Invisible Nothing

Re: empty cells not recognised as null

Fri, 03 Jul 2015 05:32:11 GMT

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

Re: empty cells not recognised as null

Anonymous — Fri, 03 Jul 2015 05:49:46 GMT

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

Re: empty cells not recognised as null

jonathandienst — Fri, 03 Jul 2015 06:17:03 GMT

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

Re: empty cells not recognised as null

Anonymous — Fri, 03 Jul 2015 07:01:09 GMT

Hi,

In script add this,

NULLASVALUE fieldname;
Set NullValue = 0;

Empty cells it will show 0

Re: empty cells not recognised as null

Sun, 05 Jul 2015 22:06:00 GMT

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.

Re: empty cells not recognised as null

MarcoWedel — Sun, 05 Jul 2015 22:16:12 GMT

another solution could be:

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

from yoursource;

hope this helps

regards

Marco

Re: empty cells not recognised as null

Peter_Cammaert — Sun, 05 Jul 2015 22:32:13 GMT

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

Re: empty cells not recognised as null

Peter_Cammaert — Sun, 05 Jul 2015 22:39:05 GMT

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

Re: empty cells not recognised as null

Sun, 05 Jul 2015 23:40:23 GMT

Hi all

Thanks for your suggestions.

I have 78 data fields (ie., 78 columns) and I would like to apply the same script to all columns. I have been using 'Group ID' in all my scripts and still this one doens't work:

LOAD if(len([Group ID]) > 0, [Group ID], NULL()) as [Group ID],

Thanks again!

Re: empty cells not recognised as null

PrashantSangle — Mon, 06 Jul 2015 05:08:32 GMT

Hi,

Try below,

load if(len(trim([Group ID]))>0,[Group ID]) as [Group ID]

Regards

Re: empty cells not recognised as null

avinashelite — Mon, 06 Jul 2015 05:24:14 GMT

if null is not recognized as null then it mean it contains a space or tab space , it won't be visible and it will not be treated as null. So trim your values and then check of the length of the field that will work.

if(len(trim(yourfieldname))>0,yourfiledname) as yourfiledname

Re: empty cells not recognised as null

Mon, 06 Jul 2015 05:46:41 GMT

Thanks guys for helping me solve my script!

Unfortunately, it's still not working ... i guess [Group ID] shouldn't be used in the first place. As i have 78 data fields, i created [Group ID], which accounts for the people in my dataset, eg., in my completeness formula: =Count([Group ID]). I guess [Group ID] is not the proper representation of the data fields.

thanks again!

Re: empty cells not recognised as null

PrashantSangle — Mon, 06 Jul 2015 05:57:41 GMT

Hi,

Can you provide your sample app with expected output??

Explain little bit more.

Regards,

Re: empty cells not recognised as null

Mon, 06 Jul 2015 22:39:24 GMT

Hi max dreamer

herewith is a sample constructed dataset extracted from an SQL database, imagine there are null values ...

Group ID	Health System Number	Name	Surname	Date of Birth	Date of Diagnosis	Usual Residential Address	Diagnosis
1	ABC1200
2	DEF1300
3	GHI1400
4	JKL1500

@Avinash R, i'm aware of the spaces or tab spaces that might be existing in the dataset after the extraction and i'd like to get rid of those spaces using scripts to recognise empty spaces.

This is a sample script:

LOAD
filename () as File,
[Group ID],
[Health System Number],
[Name],
[Surname],
[Date of Birth],
[Date of Diagnosis],
[Usual Residential Address],

Re: empty cells not recognised as null

antoniotiman — Tue, 07 Jul 2015 19:35:48 GMT

Perhaps are You loading data from MySql DB?

Re: empty cells not recognised as null

qlikviewwizard — Wed, 08 Jul 2015 02:51:00 GMT

Nice. Thanks

Re: empty cells not recognised as null

Thu, 30 Jun 2016 12:54:33 GMT

works!!! wonderful