Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
You can add it in the script level as;
IF(IsNull(Field1),'NULL',Field1) as Field1
Regards
Rajesh
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;
?
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
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
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
at the beginning of the script, or NullDisplay, if you are connecting through ODBC.NullAsValue *
Regards.