topic Re: difference between null() and '' in QlikView

difference between null() and ''

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: difference between null() and ''

arulsettu — Mon, 18 Jul 2016 07:36:19 GMT

may be like this

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

Re: difference between null() and ''

Mon, 18 Jul 2016 07:43:53 GMT

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

Re: difference between null() and ''

Mon, 18 Jul 2016 07:47:38 GMT

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

Re: difference between null() and ''

Mon, 18 Jul 2016 08:01:28 GMT

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

Re: difference between null() and ''

arulsettu — Mon, 18 Jul 2016 10:14:10 GMT

may be this

NullAsValue *;

Set NullValue = 'novalue';

in load script

Re: difference between null() and ''

marcus_sommer — Mon, 18 Jul 2016 10:41:02 GMT

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

- Marcus

Re: difference between null() and ''

Peter_Cammaert — Mon, 18 Jul 2016 10:56:03 GMT

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

Re: difference between null() and ''

ramasaisaksoft — Mon, 18 Jul 2016 12:39:15 GMT

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, ,_,

Re: difference between null() and ''

rwunderlich — Mon, 18 Jul 2016 16:49:01 GMT

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

Re: difference between null() and ''

Tue, 19 Jul 2016 02:59:03 GMT

you can use it in scripting. if(len(fieldname)=0,'no-value',fieldname) as fieldname

Re: difference between null() and ''

Wed, 20 Jul 2016 02:03:03 GMT

Thank you guys! your notes really give me comprehensive view of null() and ''.

Re: difference between null() and ''

oknotsen — Wed, 20 Jul 2016 07:56:56 GMT

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .