Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

filling NULL values created as a result of the generation of field value combinations

Hi,

As the question says, I have a table that has null values due to the field values not matching up. I want to fill the empty fields with something else. As the actual data in the fields are not null, I believe I should be doing this in the expression. The formula I used is

if (isnull ([Country]), 'Rest of the world', [Country])

However, it is not working. How should I do this?

1 Solution

Accepted Solutions
karthikoffi27se
Contributor III

Re: filling NULL values created as a result of the generation of field value combinations

Hi Chan,

I guess they are missing values rather then Null values.Please confirm by counting the missing value with the below expression


Missingcount(Expression)

try using this two

if(not isnull(expression),expression,'!')

or

if(len(trim(expression))>0,expression,'!')

Many Thanks

Karthik

10 Replies
karthikoffi27se
Contributor III

Re: filling NULL values created as a result of the generation of field value combinations

Hi Chan,

please define the NULL in the load script.

and then you can use the If condition to input your dimension value.

NULLASVALUE *;

SET vNullvalue = '<Null>';

Many Thanks

Karthik

rahulpawarb
Valued Contributor III

Re: filling NULL values created as a result of the generation of field value combinations

May be try this:

=if(Len([Country])>0, [Country], 'Rest of the world')

Regards!

Rahul Pawar

Not applicable

Re: filling NULL values created as a result of the generation of field value combinations

Hi,

I added it into the script but it didn't work. Is there any specific place i should put it at? Also the column is not actually null in the first place. The reason why the column is null in the table is because the key between the 2 tables in the database do not match sometimes.

Thanks!

Not applicable

Re: filling NULL values created as a result of the generation of field value combinations

Hi,

It did not work, probably because the fields are null and has no value? Anyway if i switch them around like this:

=if(Len([Country])>0, 'Rest of the world', [Country])

The fields that originally had results in them changed but the null fields were still blank. Any idea why?

Thanks!

rahulpawarb
Valued Contributor III

Re: filling NULL values created as a result of the generation of field value combinations

Could you please share the result of below expression for column value where it is NULL?

Len([Country])

Not applicable

Re: filling NULL values created as a result of the generation of field value combinations

Hi,

As seen in the picture below, len([Country]) shows 0 for null fields. However, the field is still blank.

Capture.PNG

Thanks!

rahulpawarb
Valued Contributor III

Re: filling NULL values created as a result of the generation of field value combinations

Considering above data below expression should work:

//Show 'Rest of the world' if country field value is null else show the available country value

=if(Len([Country])=0, 'Rest of the world', [Country])

karthikoffi27se
Contributor III

Re: filling NULL values created as a result of the generation of field value combinations

Hi Chan,

I guess they are missing values rather then Null values.Please confirm by counting the missing value with the below expression


Missingcount(Expression)

try using this two

if(not isnull(expression),expression,'!')

or

if(len(trim(expression))>0,expression,'!')

Many Thanks

Karthik

Not applicable

Re: filling NULL values created as a result of the generation of field value combinations

Hi,

Strangely this does not work either. The field is still blank. However if I do the same on another table with the same field but under a different name, it works fine.

The difference between the tables is that in the table above, the data pulled is from the same source, but for the table in my question, the data is pulled from 2 sources.

Thanks!

Community Browser