Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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"
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';
Hi,
try this..
NullAsValue *;
SET NullValue ="NA";
Tablename:
LOAD
field 1,
field 2
FROM ...............
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"
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...
This Works, but is this the optimal way of doing it?
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.
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