Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello guys,
I'm trying to use NullAsValue to convert data without values to some named string, but to some field, it didn't work until I use express like below to create a substitue field. my question is does QV create "blank value"(here is '') other than Null() during my JOIN, or my source data base contains "blank value", and how should I tell when NullAsValue can totally work before I try on checking the existence of "blank value".
If(*fieldname='','no value, fieldname)
thank you.
Jia Junqing
may be like this
if(isnull(fieldname),'Novalue',fieldname)
if(len(fieldname)=0,'no-value',fieldname)
hello, Arul,
thank you for your attention.
my problem is IsNull() didn't work, because the field contains '' instead of Null(). when you use IsNull trying to flag lines with no data in the field, it didn't work, but if you use *fieldname='' to flag then it worked. so my question is in QV, '' doesn't equal to Null()? and how should I replace '' to some named value/string easily.
Jia Junqing
hello Jacky,
it works, thank you. but do you know if there's any way simple like NullAsValue? to set at the beginning of the script then replace both '' and Null() to some value/string?
Jia Junqing
may be this
NullAsValue *;
Set NullValue = 'novalue';
in load script
The best explanation and what you could do in which cases will you find here: NULL handling in QlikView.
- Marcus
QlikView, being largely a database-oriented application, inherits the concepts of nothing-invisible empty string-NULL from those data storage tools.
The empty string '' is still a string, while NULL is an artificial indicator that tells you that the data doesn't exist or a field is uninitialized. A NULL value means: nothing there, there is no data, no value, not even an empty string or a single space character (which is quite invisible as well).
To grab everything that in our minds comes down to "nothing", we often use a technique like:
:
IF (len(trim(ColumnToCheck)) > 0, ColumnToCheck) AS ColumnChecked,
:
to store NULL instead of empty strings, whitespaces, Null values etc.
Best,
Peter
Hi Jia,
Null() vs ''
Null () will removes empty records(-),space bars data( ),zero values(0),underscore,hi-fen ...etc
but '' will removes only one space bar.
may be i am 50 % correct so please verify yourself.
check with sample data 0, ,_,
You can extend the technique shown here to map both null() and '' to a string.
Filling Default Values Using Mapping | Qlikview Cookbook
Note that you don't have to do that extra load at the end unless nulls have been created by Join. If you are just doing regular loads, the following statements should do it. (where fieldname is your field). You can use the same map for multiple fields, Just add more MAP statements.
NullMap:
MAPPING
LOAD Evaluate(from), to INLINE
[
from, to
null(), NoValue
trim(''), NoValue
];
MAP fieldname USING NullMap;
-Rob