topic Re: How to display NULL values as 'NULL' instead of '-' in QlikView

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

Thu, 26 Nov 2009 00:55:23 GMT

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

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

Miguel_Angel_Baeyens — Thu, 26 Nov 2009 01:01:18 GMT

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

NullAsValue *;

at the beggining of your script.

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

johnw — Thu, 26 Nov 2009 01:15:09 GMT

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.

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

Thu, 26 Nov 2009 01:15:39 GMT

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 ...

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

Thu, 26 Nov 2009 01:43:29 GMT

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;

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

Thu, 26 Nov 2009 03:00:39 GMT

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...

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

Wed, 20 Jun 2012 14:50:14 GMT

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)

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

Jason_Michaelides — Wed, 20 Jun 2012 19:44:25 GMT

The SET needs to come before the LOAD.

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

Thu, 21 Jun 2012 05:58:30 GMT

Hi,

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

NULLASVALUE *;

SET NullValue ='NULL' ;

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

Anonymous — Tue, 28 Aug 2012 00:07:53 GMT

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

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

whiteline — Tue, 28 Aug 2012 10:00:34 GMT

EndDate = NULL

The right way to test if the value is null is function ISNULL.

Use WHERE IsNull(EndDate) and StartDate <= Today();