Handling NULL in a simple report

[#1]

Hi,

I created a simple report for practice which will fetch all data from a table as follows

EMPID     ENAME     DEPTNO

1              James          10

2              Tim               40

3               Lynn            NULL

4               Jack            30

5               Martin          NULL

Now when I generate a qlikview report, I see '-' in place of NULL. However I need to substitute that '-' with 'Not Applicable'

Can you suggest solution to handle this at report level?

Regards,

Darpan

[Gysbert_Wassenaar]

All you need to know about dealing with nulls can be found in this document: NULL handling in QlikView

For database sources you can replace nulls using the NullDisplay variable:

     Set NullDisplay = 'Not Applicable';

For text file sources you use this:

     NullAsValue *;

     Set NullValue = 'Not Applicable';

[#3]

Hi,

try this..

NullAsValue *;

SET NullValue ="NA";

Tablename:

LOAD

field 1,

field 2 

FROM ...............

[#4 · Accepted solution]

Hi,

Use this script

Tab2;

Load  EMPID,

          ENAME,

           DEPTNO

           from

              .............................

NoConcatenate

Load EMPID,

     ENAME,

     if(not isnull(DEPTNO),DEPTNO,'Not Applicable') as DEPTNO

     Resident Tab2;

     Drop table Tab2;

This will replace all the null value with "Not Applicable"

[#5]

I did this however it is not working. Might me I am going wrong somewhere.

I added the below code snippet in report script before I loaded the QVD for the report:

NullAsValue *;

Set NullValue = 'Not Applicable';

LOAD col1,

col2,

.

.

coln

FROM.......qvd;

Let me know where am I going wrong...

[#6]

This Works, but is this the optimal way of doing it?

[Gysbert_Wassenaar]

Ah, you're loading from qvd files. Those are a bit special. It's explained in the document I referred to. Apparently you did not read it yet. From that document:

There are however several things to be careful of when using NullAsValue. The first one is when

you load tables using optimized QVD loads. An optimized load will not change the data within the

QVD and will hence not convert the NULLs. You will need to force QlikView to load the data

unoptimized. A simple where-clause will do the trick, but I prefer to write code that is more explicit;

so I remember why I did it when I look at the script a year later. My suggestion is hence:

Load If( Len( Trim( Field ) ) > 0, Field, '$(NullValue)' ) as NewField …

The optimal way is to prevent nulls from ending up in the qvd in the first place. The next best option is to replace the nulls as shown in the above quote.

[er_mohit]

Try this in existing table because ist it load all the data in memory then again load from in memory data using resident load it takes time if your data having millions of record.

so try this with existing table like this

Tab1;

Load  EMPID,

          ENAME,

           if(len(trim(DEPTNO))=0,'Not Applicable',DEPTNO) as DEPTNO

           from

              .....datasource;........................;

hope it helps