Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Could some one help me
From Data Source how to avoid Null Values one interviewer asked this question ,
i said suppress zero
He is asking How to avoid through scrip
Thanks in Advance,
Niranjan
Hi Niru,
try this in edit script if you like to make null value as zero in back-end.
NullAsValue fieldname;
Set NullValue = 'NULL';
then in load script type:
if(isNull(field)=-1,0,field) as newfield
example:
test:
load * Inline [
name, value1
A, NULL
D, NULL
B, 26
B, 164
E, 88
D, 58
C, NULL
C, 124
A, 31
C, 127
E, 188
B, NULL
D, 90
C, 63
A, 178
E, 82
B, 41
D, 199
E, 55
B, NULL
D, 197
C, 178
D, 79
E, NULL
];
NULLASVALUE value1;
Set NullValue = 'NULL';
Load *,
if(IsNull(value1)=-1,0,value1)as testnull
Resident test;
Drop Table test;
hope it can help
Regards,
To avoid null in script,
Provide a where condition as
where len(trim(fieldname))>0;
This wil remove the null values from the field.
Hi,
In script you can use a "where" clause or "IsNull" to avoid loading Null values from data source.
Hope this helps!
Hi,
How can i use IsNull function in Scrip
Thanks,
Niranjan
My bad.
IsNull can be used in Expressions, in If conditions or Set Analysis etc.
In where condition you can also use
where field name <> null();
Regards
Nitin
If the null has to be checked while getting data directly from DB, you have to use a fuction that goes with SQL like:
Select FieldA,
FieldB
From <db> where FieldA IS NOT NULL;
If to be used in QV script then can try like:
Load FieldA,
FieldB
From <db> where Not IsNull(FieldA);
While writing Load Statement, check value whether it is Null or Not.
Like:
IF (NOT isNull(filetime('$(sFile)' ))) THEN
......
.......
END IF
Hi Niru,
try this in edit script if you like to make null value as zero in back-end.
NullAsValue fieldname;
Set NullValue = 'NULL';
then in load script type:
if(isNull(field)=-1,0,field) as newfield
example:
test:
load * Inline [
name, value1
A, NULL
D, NULL
B, 26
B, 164
E, 88
D, 58
C, NULL
C, 124
A, 31
C, 127
E, 188
B, NULL
D, 90
C, 63
A, 178
E, 82
B, 41
D, 199
E, 55
B, NULL
D, 197
C, 178
D, 79
E, NULL
];
NULLASVALUE value1;
Set NullValue = 'NULL';
Load *,
if(IsNull(value1)=-1,0,value1)as testnull
Resident test;
Drop Table test;
hope it can help
Regards,
load
a,
b,
c
where len(trim(yourfield)) <> 0 or not isNull(yourfield);
anant