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

[#1]

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?

[karthikoffi27se]

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]

May be try this:

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

Regards!

Rahul Pawar

[#4]

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!

[#5]

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]

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

Len([Country])

[#7]

Hi,

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

Thanks!

[rahulpawarb]

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 · Accepted solution]

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]

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!