difference between null() and ''

[#1]

hello guys,

I'm trying to use NullAsValue to convert data without values to some named string, but to some field, it didn't work until I use express like below to create a substitue field. my question is does QV create "blank value"(here is '') other than Null() during my JOIN, or my source data base contains "blank value", and how should I tell when NullAsValue can totally work before I try on checking the existence of "blank value".

If(*fieldname='','no value, fieldname)

thank you.

Jia Junqing

[arulsettu]

may be like  this

if(isnull(fieldname),'Novalue',fieldname)

[#3]

if(len(fieldname)=0,'no-value',fieldname)

[#4]

hello, Arul,

thank you for your attention.

my problem is IsNull() didn't work, because the field contains '' instead of Null(). when you use IsNull trying to flag lines with no data in the field, it didn't work, but if you use *fieldname='' to flag then it worked. so my question is in QV, '' doesn't equal to Null()? and how should I replace '' to some named value/string easily.

Jia Junqing

[#5]

hello Jacky,

it works, thank you. but do you know if there's any way simple like NullAsValue? to set at the beginning of the script then replace both '' and Null() to some value/string?

Jia Junqing

[arulsettu]

may be this

NullAsValue *;

Set NullValue = 'novalue';

in load script

[marcus_sommer]

The best explanation and what you could do in which cases will you find here: NULL handling in QlikView.

- Marcus

[Peter_Cammaert]

QlikView, being largely a database-oriented application, inherits the concepts of nothing-invisible empty string-NULL from those data storage tools.

The empty string '' is still a string, while NULL is an artificial indicator that tells you that the data doesn't exist or a field is uninitialized. A NULL value means: nothing there, there is no data, no value, not even an empty string or a single space character (which is quite invisible as well).

To grab everything that in our minds comes down to "nothing", we often use a technique like:

:

IF (len(trim(ColumnToCheck)) > 0, ColumnToCheck) AS ColumnChecked,

:

to store NULL instead of empty strings, whitespaces, Null values etc.

Best,

Peter

[ramasaisaksoft]

Hi Jia,

Null() vs ''

Null () will removes empty records(-),space bars data(   ),zero values(0),underscore,hi-fen ...etc

but '' will removes only one space bar.

may be i am 50 % correct so please verify yourself.

check with sample data 0,   ,_,

[rwunderlich]

You can extend the technique shown here to map both null() and '' to a string.

Filling Default Values Using Mapping | Qlikview Cookbook

Note that you don't have to do that extra load at the end unless nulls have been created by Join. If you are just doing regular loads, the following statements should do it. (where fieldname is your field). You can use the same map for multiple fields, Just add more MAP statements.

NullMap:

MAPPING

LOAD Evaluate(from), to INLINE

[

from, to

null(), NoValue

trim(''), NoValue

];

MAP fieldname USING NullMap;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com