Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
NavinReddy
Creator II
Creator II

How to Avoid Null Values

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

1 Solution

Accepted Solutions
Anonymous
Not applicable

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,

View solution in original post

10 Replies
Anonymous
Not applicable

To avoid null in script,

Provide a where condition as

where len(trim(fieldname))>0;

This wil remove the null values from the field.

Not applicable

Hi,

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

Hope this helps!

NavinReddy
Creator II
Creator II
Author

Hi,

How can i use IsNull function in Scrip

Thanks,

Niranjan

Not applicable

My bad.

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

Anonymous
Not applicable

In where condition you can also use

where field name <> null();

Regards

Nitin

tresesco
MVP
MVP

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
Creator II
Creator II

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

Like:

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

......

.......

END IF

Anonymous
Not applicable

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
Not applicable

load

a,

b,

c

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

anant