topic Re: Handling NULL in a simple report in QlikView

Handling NULL in a simple report

Tue, 08 Jul 2014 09:28:27 GMT

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

Re: Handling NULL in a simple report

Gysbert_Wassenaar — Tue, 08 Jul 2014 09:29:59 GMT

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';

Re: Handling NULL in a simple report

Tue, 08 Jul 2014 09:33:58 GMT

Hi,

try this..

NullAsValue *;

SET NullValue ="NA";

Tablename:

LOAD

field 1,

field 2

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

Re: Handling NULL in a simple report

Tue, 08 Jul 2014 09:52:50 GMT

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"

Re: Handling NULL in a simple report

Tue, 08 Jul 2014 09:54:58 GMT

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

Re: Handling NULL in a simple report

Tue, 08 Jul 2014 10:08:21 GMT

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

Re: Handling NULL in a simple report

Gysbert_Wassenaar — Tue, 08 Jul 2014 10:17:54 GMT

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.

Re: Handling NULL in a simple report

er_mohit — Tue, 08 Jul 2014 10:33:37 GMT

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