Replace Null Values

[#1]

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.

[#2]

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

[suniljain]

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

[#4]

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

[#5]

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

[Anonymous]

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)

[#7]

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.