topic Replace Null Values in QlikView

Replace Null Values

Mon, 14 Feb 2011 18:09:35 GMT

Want to change on Script Side and not In Expression.

1) Tried changing Setnullasvalue;

2) Tried IF (Isnull(Name), ' NoName', 'Name')

Unable to replace nullvalues with 'NoName'

Any Help Plz.

Replace Null Values

Mon, 14 Feb 2011 18:34:00 GMT

I found an answer to this problem earlier today when trying to replace NULLs with blanks - can't remember who it was posted by.

Try adding the following to your script before loading:-

NullMap:

MAPPING LOAD

null(),'NoName'

AUTOGENERATE 1;

Then after loading the nulls:

MAP <nullfield> using NullMap;

right join (tablename) load distinct * resident tablename;

Not eaxctly clear how it works (I'm a newbie myself), but it seems to do the trick.

Regards

Graham

Replace Null Values

suniljain — Mon, 14 Feb 2011 18:38:23 GMT

if(Name='','NoName','Name')

Replace Null Values

Mon, 14 Feb 2011 19:59:06 GMT

dude,

if you want to handle NULLS in the script side...then it depends on the database from which (if at all ur source is database tables) you are loading values to qlikview.

for MS SQL db i guess 'isnull' would work...

i worked with MS SQL and then later when i started querying in db2 i found the db2 equivalent as

COALESCE

[The COALESCE function can also handle a subset of the functions provided by CASE expressions. The result of using COALESCE(e1,e2) is the same as using the expression:

CASE WHEN e1 IS NOT NULL THEN e1 ELSE e2 END ]

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/fcoal.htm

Replace Null Values

Mon, 14 Feb 2011 20:55:39 GMT

Its a mapping Cool I am not sure why a right join is needed ?

Replace Null Values

Anonymous — Mon, 14 Feb 2011 20:56:24 GMT

VinKakarla,

Your 2nd version is correct, with minor change:
IF(Isnull(Name), ' NoName', Name)
And, sometimes isnull() function doesn't work properly, so you can use this variation:
IF(len(trim(Name))=0, ' NoName', Name)

Re: Replace Null Values

Wed, 06 Jul 2016 10:55:49 GMT

Another possible way to convert nulls and zeros into zeros is to use the RangeSum() function :

RangeSum("FieldName") as Field

Any nulls will get converted into zeros.