topic Filling in null values in a table in QlikView

Filling in null values in a table

Tue, 20 Apr 2010 23:01:44 GMT

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

Filling in null values in a table

Tue, 20 Apr 2010 23:27:41 GMT

Hi,

You can add it in the script level as;

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

Regards

Rajesh

Filling in null values in a table

Wed, 21 Apr 2010 18:51:43 GMT

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;

Filling in null values in a table

achiever_ajay — Thu, 22 Apr 2010 04:30:49 GMT

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

Filling in null values in a table

Thu, 22 Apr 2010 07:13:59 GMT

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

Filling in null values in a table

Miguel_Angel_Baeyens — Thu, 22 Apr 2010 07:39:53 GMT

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.