How to Avoid Null Values

[NavinReddy]

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

[Anonymous]

To avoid null in script,

Provide a where condition as

where len(trim(fieldname))>0;

This wil remove the null values from the field.

[#3]

Hi,

In script you can use a "where" clause or "IsNull" to avoid loading Null values from data source.

Hope this helps!

[NavinReddy]

Hi,

How can i use IsNull function in Scrip

Thanks,

Niranjan

[#5]

My bad.

IsNull can be used in Expressions, in If conditions or Set Analysis etc.

[Anonymous]

In where condition you can also use

where field name <> null();

Regards

Nitin

[tresesco]

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

[ramkrishna86]

While writing Load Statement, check value whether it is Null or Not.

Like:

IF (NOT isNull(filetime('$(sFile)' ))) THEN

......

.......

END IF

[Anonymous · Accepted solution]

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,

[Anonymous]

load

a,

b,

c

  where  len(trim(yourfield)) <> 0 or  not isNull(yourfield);

anant