Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
cheburashka
Contributor III

How to replace Null() by a string value?

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

1 Solution

Accepted Solutions
cheburashka
Contributor III

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

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

View solution in original post

6 Replies
Not applicable

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

Partner
Partner

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

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
Contributor III

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

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

View solution in original post

Not applicable

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

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
Contributor III

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

Not applicable

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

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