topic Re: How to Avoid Null Values in QlikView

How to Avoid Null Values

NavinReddy — Mon, 16 Dec 2013 06:20:10 GMT

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

Re: How to Avoid Null Values

Anonymous — Mon, 16 Dec 2013 06:24:35 GMT

To avoid null in script,

Provide a where condition as

where len(trim(fieldname))>0;

This wil remove the null values from the field.

Re: How to Avoid Null Values

Mon, 16 Dec 2013 06:26:23 GMT

Hi,

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

Hope this helps!

Re: How to Avoid Null Values

NavinReddy — Mon, 16 Dec 2013 06:29:57 GMT

Hi,

How can i use IsNull function in Scrip

Thanks,

Niranjan

Re: How to Avoid Null Values

Mon, 16 Dec 2013 06:38:21 GMT

My bad.

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

Re: How to Avoid Null Values

Anonymous — Mon, 16 Dec 2013 06:49:37 GMT

In where condition you can also use

where field name <> null();

Regards

Nitin

Re: How to Avoid Null Values

tresB — Mon, 16 Dec 2013 07:24:17 GMT

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

Re: How to Avoid Null Values

ramkrishna86 — Mon, 16 Dec 2013 07:31:00 GMT

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

Like:

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

......

.......

END IF

Re: How to Avoid Null Values

Anonymous — Mon, 16 Dec 2013 07:34:49 GMT

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,

Re: How to Avoid Null Values

Anonymous — Mon, 16 Dec 2013 07:49:04 GMT

load

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

anant

Re: How to Avoid Null Values

Mon, 16 Dec 2013 08:23:27 GMT

Hello,

You can have look at this:

http://community.qlik.com/docs/DOC-3155

Thanks,

Angad