Filling in null values in a table

[#1]

Hi guys. I have a table with a number of fields and those fields could have null values. I want to replace those null values with a string 'null' so I can select them. Is there an easy way to do this once the table is resident in QlikView? Thanks!

Eric

[#2]

Hi,

You can add it in the script level as;

IF(IsNull(Field1),'NULL',Field1) as Field1

Regards

Rajesh

[#3]

So say I already have the table loaded in the script. Would I do a left join on that table or something? So like this:

LEFT JOIN (MyTable)

LOAD

ID,

If(IsNull(Field1,'None',Field1) AS Field1,

If(IsNull(Field2,'None',Field2) AS Field2,

...

RESIDENT MyTable;

?

[achiever_ajay]

hi,

in your edit script, you can direclty replace the line where you load "field1" with "If(IsNull(Field1,'None',Field1) AS Field1," thix would be enough.

eg..

//old script

load Field1

from ....

//new script

load If(IsNull(Field1,'None',Field1) AS Field1,

from...

Regards

Ajay Kumar

[#5]

Hi,

One more suggestion would be instead of using isnull() function. try using len() function since it would detect both null and blank value and in some case isnull() function may not detect null as well. so len() function would be advisable.

you formula would be if(len(Field1) = 0 ,'None',Field1) as Field1

- Sridhar

[Miguel_Angel_Baeyens]

Hello,

I prefer to use Len() instead of IsNull() as well. But if you need to do that in all fields of your datasource, you can check the use of

NullAsValue *

at the beginning of the script, or NullDisplay, if you are connecting through ODBC.

Regards.