How to display NULL values as 'NULL' instead of '-'

[#1]

Hi all. I've got some charts with null values in them. By default, QlikView displays null values as '-'. This doesn't make any sense to my users, so I want it to say 'NULL' instead. I tried a number of things, SET NullValue = 'NULL'; or SET NullDisplay = 'NULL'; but neither worked. Does anyone know how to do this? Thanks!

Eric

[Miguel_Angel_Baeyens]

Those variable will only work if NullAsValue is activated for those fields. Try starting with

NullAsValue *;

at the beggining of your script.

[johnw]

You could also override the display of nulls on each chart. On the Presentation tab, there should be enterable fields for what you want displayed for null and missing values. But I'm sure there's an easier way. I haven't tried what Miguel is suggesting, so the answer might be just that easy.

Another not as easy way is to override the nulls with an actual value of 'NULL' in the script. But that can be inconvenient, because it is then treated as a normal value like any other. I've done that in a case where the users wanted 'Missing' when it was null and should have been there, and 'NA' when it was null but this was expected.

[#4]

That didn't seem to work either. Here's what the beginning of my program looks like:

NullAsValue *;
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET NullValue='<NULL>';
SET NullDisplay='Null';

ODBC CONNECT TO ...

[#5]

Hi
I had this problem and put it in the load script because I did want to see records with / and be able to select records that had #Nulls. I put this code into the QVW that created my QVD data source.

Hope this helps
Greenee

Table_A:
LOAD
"FIELD" AS FIELD;
SET NULLDISPLAY='#Null' ;
SQL SELECT *
FROM DATABASE_TABLE
WHERE etc etc ;
STORE Table_A into D:\\QVDs\Table_A.QVD;
Drop Table Table_A;

[#6]

Greenee is correct. Using SET NULLDISPLAY will replace all NULL values in the fields you load with the value you give it.

Be carefull with this, because it will do it for ALL the fields you load after the SET expression.

You can also use a new SET NULLDISPLAY after each table that is loaded to set a new NULL value for each table. It can help in determining where the NULL values are coming from...

[#7]

I have a similar issue but need to categorize all null values in a table as All Other. Here is what my script looks like. It is not working. Any suggestions?

LOAD

Part#,

[Part Category] as Part_Category,

SET NULLDISPLAY='All-Other';


FROM

FWAR904Final.qvd

(qvd)

[Jason_Michaelides]

The SET needs to come before the LOAD.

[#9]

Hi,

You need to write the below two lines before the load statement.

NULLASVALUE *;

SET NullValue ='NULL' ;

[Anonymous]

OK, the Nulls displayed and worked well but this doesn't work on the loads exclude I noticed.  Does anyone know how to restrict the Null's when loading from from a QVD?

Directory;

NULLASVALUE *;

SET NullValue ='NULL' ;

LOAD

     Value1,

      Value2,

      StartDate,

      EndDate

FROM

[..\..\Sample.qvd]

(qvd)

WHERE  EndDate = NULL and StartDate <= Today();

Thanks