Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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..
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
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
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
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