Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Handling NULL in a simple report

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

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Handling NULL in a simple report

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"

View solution in original post

7 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Handling NULL in a simple report

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


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: Handling NULL in a simple report

Hi,

try this..

NullAsValue *;

SET NullValue ="NA";

Tablename:

LOAD

field 1,

field 2 

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

Highlighted
Not applicable

Re: Handling NULL in a simple report

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"

View solution in original post

Highlighted
Not applicable

Re: Handling NULL in a simple report

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

Highlighted
Not applicable

Re: Handling NULL in a simple report

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Handling NULL in a simple report

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.


talk is cheap, supply exceeds demand
Highlighted
Master II
Master II

Re: Handling NULL in a simple report

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