Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Niru
Contributor 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
suryanto89
New Contributor III

Re: How to Avoid Null Values

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,

10 Replies
gupta_n8
Valued Contributor II

Re: How to Avoid Null Values

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

Re: How to Avoid Null Values

Hi,

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

Hope this helps!

Niru
Contributor II

Re: How to Avoid Null Values

Hi,

How can i use IsNull function in Scrip

Thanks,

Niranjan

Not applicable

Re: How to Avoid Null Values

My bad.

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

gupta_n8
Valued Contributor II

Re: How to Avoid Null Values

In where condition you can also use

where field name <> null();

Regards

Nitin

MVP
MVP

Re: How to Avoid Null Values

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

Re: How to Avoid Null Values

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

Like:

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

......

.......

END IF

suryanto89
New Contributor III

Re: How to Avoid Null Values

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,

anantmaxx
Contributor III

Re: How to Avoid Null Values

load

a,

b,

c

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

anant

Community Browser