topic Re: How to replace Null() data by a value using mapping? in QlikView

How to replace Null() by a string value?

cheburashka — Wed, 20 Feb 2013 15:35:45 GMT

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

Re: How to replace Null() data by a value using mapping?

Wed, 20 Feb 2013 22:01:04 GMT

Look here

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

Re: How to replace Null() data by a value using mapping?

shree909 — Wed, 20 Feb 2013 22:47:59 GMT

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..

Re: How to replace Null() data by a value using mapping?

cheburashka — Wed, 24 Dec 2014 10:23:39 GMT

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

Re: How to replace Null() data by a value using mapping?

Wed, 18 Mar 2015 15:08:39 GMT

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,

Re: How to replace Null() data by a value using mapping?

cheburashka — Thu, 19 Mar 2015 10:31:10 GMT

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

Re: How to replace Null() by a string value?

Thu, 19 Mar 2015 19:25:32 GMT

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,