How to replace Null() by a string value?

[cheburashka]

Hello,

I'm trying to replace null() values of a field [Rekening tegenpartij] by the value 'geen' .

I tried the script below. The script compiles but is not changing anything in the data.

1) What is wrong in my script? Is there anything wrong with defining the Null() value?

2) Is there another more elegant way not using the mapping function?

     Considering that I will also use the same mappingtable to replace empty values of other fields of the table Transacties.

//create table to be used in mapping
mappingtable:
Mapping LOAD * INLINE
[     Key, Label
      Null(), geen   ];

//Map the empty values in Rekening tegenpartij
LOAD
ApplyMap( 'mappingtable' , [Rekening tegenpartij] ) as [Rekening tegenpartij]
RESIDENT Transacties;

Thx for your help

_Koen

[#2]

Look here

http://qlikviewnotes.blogspot.ca/2013/01/filling-default-values-using-mapping.html

[shree909]

i think u can use isnull fucntion to replcase the null values with the green

if( isnull([Rekening tegenpartij]) = -1 , green, [Rekening tegenpartij] ) as  [Rekening tegenpartij]

above fuction will look for the nullvalues and replace it with the green..

[cheburashka · Accepted solution]

Hello,

I have made a small example with the solution.

Please find the solution attached.

The following code (similar to the one of Shree ana) was used:

if(IsNUll([Field with Nulls]), 'NA', [Field with Nulls]) as [Nulls converted to NA]  

,KR Koen

[#5]

Hi Koen,

Your code works great when the nulls are created when there is missing data in a table created in the LoadScript. However, I can't get your solution to replace the nulls if the missing data is loaded from an XLSX file. See attached. Any ideas what I am doing wrong?

Thanks,

CL

[cheburashka]

Are you sure you are loading Nulls and not empty values or blanks?

Have you used the trim function, to remove the blanks?

Also check out:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/30/null-the-invisible-nothing

http://community.qlik.com/docs/DOC-3155

http://community.qlik.com/blogs/qlikviewdesignblog/2012/06/28/the-importance-of-nothing

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/02/finding-null

, KR Koen

[#7]

They appear as '-' in my ReplaceNullsTestApp.qvw table.  I did try checking for Len(Trim(test_string))<=0, but I get the same '-' results. Any other ideas?

Thanks,

CL