topic Re: Null as zero in QlikView

Null as zero

manoranjan_d — Thu, 12 May 2016 08:36:29 GMT

How to convert the Null values as Zero in the Qvd Generator file

Re: Null as zero

sunny_talwar — Thu, 12 May 2016 08:37:19 GMT

May be like this:

If(Len(Trim(FieldName)) = 0, 0, FieldName) as FieldName

Re: Null as zero

sunny_talwar — Thu, 12 May 2016 08:37:40 GMT

Or this:

Alt(FieldName, 0) as FieldName

Re: Null as zero

MK_QSL — Thu, 12 May 2016 08:41:08 GMT

If(IsNull(FieldName) or Len(Trim(FieldName))=0,0,FieldName) as FieldName

Re: Null as zero

manoranjan_d — Thu, 12 May 2016 08:44:43 GMT

if i go in this way i shd do for all fileds, i wan to do for all fileds in one single shot, any idea?

Re: Null as zero

ankit777 — Thu, 12 May 2016 08:45:58 GMT

You can try nullasvalue function

see below for reference

NullAsValue | Qlik Community

Re: Null as zero

MK_QSL — Thu, 12 May 2016 08:50:23 GMT

Use NullAsValue function.

Re: Null as zero

sunny_talwar — Thu, 12 May 2016 08:54:49 GMT

Look at the sample:

NullAsValue *;

SET NullValue = 0;

Table:

LOAD If(Len(Trim(Val1)) > 0, Val1) as Val1,

If(Len(Trim(Val2)) > 0, Val2) as Val2,

If(Len(Trim(Val3)) > 0, Val3) as Val3;

LOAD * Inline [

Val1, Val2, Val3

3, , 4

2, 4,

, 2, 4

];

Re: Null as zero

Anonymous — Thu, 12 May 2016 08:56:10 GMT

try this one

Alt(FieldName, 0) as FieldName

or try in this way

if(fieldname=null, 0)

Re: Null as zero

manoranjan_d — Thu, 12 May 2016 08:59:58 GMT

Temp

LOAD Emp name,

Emp ID,

Desg;

SQL SELECT *

FROM emp;

this is my example code now where shd use this null function, any idea

Re: Null as zero

sunny_talwar — Thu, 12 May 2016 09:04:07 GMT

May be like this:

NullAsValue *;

SET NullValue = 0;

Temp

LOAD Emp name,

Emp ID,

Desg;

SQL SELECT *

FROM emp;

Re: Null as zero

manoranjan_d — Thu, 12 May 2016 09:39:04 GMT

Thanks

Re: Null as zero

manoranjan_d — Fri, 13 May 2016 05:41:36 GMT

If i want to do Null as 0 for one field below is correct?

if(amount=null, 0,amount) as amount

Re: Null as zero

settu_periasamy — Fri, 13 May 2016 05:44:26 GMT

Hi,

it should be like

=if(isnull(amount),0,amount) as amount

Re: Null as zero

Fri, 13 May 2016 05:46:20 GMT

Yes it's correct...!

Re: Null as zero

Anonymous — Fri, 13 May 2016 06:08:57 GMT

yes

load qvd file or any other file if the particular field is the have the null values to give in script

if(amount=null, 0,amount) as amount

or try in this way

replace(amount,'null','0') as amount

Re: Null as zero

tejasp47 — Mon, 13 Mar 2017 16:11:35 GMT

I was trying to compare the 2 fields -

Data, where comparison was with value and NULL or NOTHING field.

I think many of the above solution may work to achieve my work. But I tried Alt function and it worked for me.

I used the complex formula for the KPI field, but the simple form of it -

Field1 Field2 Alt (Field1, 0) - Alt(Field2, 0)

-- 78% -78%

10% 20% -10%

Re: Null as zero

vishsaggi — Mon, 13 Mar 2017 17:08:09 GMT

May be this?

IF(Len(Trim(amount)) = 0, 0, amount) AS Amount