topic Re: How to replace null for 0 value in script in QlikView

How to replace null for 0 value in script

Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

I'm trying to replace missing value (null) by zero (0), but the script does not work. See here the script:

date:
LOAD * INLINE
[
date
12-03-2016
19-03-2016
15-03-2016
]
;
notification:
LOAD * INLINE
[
Notification, date
123, 12-03-2016
134, 12-03-2016
765, 15-03-2016
]
;
test:
LOAD
COUNT(Notification) as Complaints,
date
Resident notification group by date;

DROP Table notification;

Test2:
left JOIN (date)
LOAD
date,
if(isNull(Complaints), 0, Complaints) as Complaints
Resident test;

Drop Table test;

Could you please advice?

Best regards,

Cornelis

Re: How to replace null for 0 value in script

Anonymous — Mon, 28 Mar 2016 08:51:10 GMT

Try :

alt ( COUNT(Notification) , 0 ) as Complaints,

Re: How to replace null for 0 value in script

Chanty4u — Mon, 28 Mar 2016 08:52:04 GMT

set nullvalue= ' ';

Re: How to replace null for 0 value in script

swuehl — Mon, 28 Mar 2016 08:52:12 GMT

You'll need to check for and replace the missing value after the JOIN, in a subsequent resident LOAD.

Or use Applymap() with an optional default value instead.

Re: How to replace null for 0 value in script

Chanty4u — Mon, 28 Mar 2016 08:54:01 GMT

load,

.......

Where not IsNull(YourField);
or

LOAD ,

Fields

FROM ...

WHERE LEN(TRIM(Field));

Re: How to replace null for 0 value in script

tyagishaila — Mon, 28 Mar 2016 09:21:08 GMT

you can use ApplyMap() for this,

Try it,

date:

LOAD * INLINE

[

date

12-03-2016

19-03-2016

15-03-2016

];

notification:

LOAD * INLINE

[

Notification, date

123, 12-03-2016

134, 12-03-2016

765, 15-03-2016

]

;

test:

LOAD

date,

COUNT(Notification) as Complaints

Resident notification group by date;

DROP Table notification;

Test2:

mapping load

date,Complaints

Resident test;

LOAD

date,

Applymap('Test2',date,'0')as Complaints

Resident date;

Re: How to replace null for 0 value in script

Kushal_Chawda — Mon, 28 Mar 2016 09:38:04 GMT

Take one more resident load to evaluate the condition

Test 3:

LOAD *

date,
if(isNull(Complaints), 0, Complaints) as Complaints

Resident Test2;

Drop Table Test2;

Re: How to replace null for 0 value in script

veidlburkhard — Mon, 28 Mar 2016 10:11:45 GMT

Hi Cornelis,

try this:

date:

LOAD * INLINE

[

date

12-03-2016

19-03-2016

15-03-2016

]

;

notification:

LOAD * INLINE

[

Notification, date

123, 12-03-2016

134, 12-03-2016

765, 15-03-2016

]

;

test:

Mapping

LOAD

date,

COUNT(Notification) as Complaints

Resident notification group by date;

LOAD

ApplyMap('test', date, 0) as Complaints,

date

Resident date;

DROP Table date;

This gives you the 0, where no complaints are mesured.

Hope this helps

Burkhard

Re: How to replace null for 0 value in script

Mon, 28 Mar 2016 12:03:26 GMT

Hi Try this,

date:

LOAD * INLINE

[

date

12-03-2016

19-03-2016

15-03-2016

]

;

notification:

LOAD * INLINE

[

Notification, date

123, 12-03-2016

134, 12-03-2016

765, 15-03-2016

]

;

test:

LOAD

COUNT(Notification) as Complaints,

date

Resident notification group by date;

DROP Table notification;

Test2:

left JOIN (date)

LOAD

date,

Complaints

//if(isNull(Complaints), 0, Complaints) as Complaints

Resident test;

Drop Table test;

NULLASVALUE Complaints;

set Nullvalue=0;

NoConcatenate

date1:

LOAD date,

Complaints

Resident date;

DROP Table date;

Re: How to replace null for 0 value in script

avinashelite — Mon, 28 Mar 2016 12:06:36 GMT

Try like this

Result:

LOAD *,

if(len(trim(Complaints))=0, 0, Complaints) as New_Complaints
Resident Test2;
Drop Table test;

Drop Table Test2;

Re: How to replace null for 0 value in script

dusasuman — Mon, 28 Mar 2016 12:21:58 GMT

See the attached qlikview file. Resolved using applymap() function.

Regards

Suman

Re: How to replace null for 0 value in script

Mon, 28 Mar 2016 14:21:18 GMT

Hi,

Try following Script.

Date:

LOAD * INLINE

[

date

12-03-2016

19-03-2016

15-03-2016

]

;

LEFT JOIN

notification:

LOAD * INLINE

[

Notification, date

123, 12-03-2016

134, 12-03-2016

765, 15-03-2016

]

;

test:

LOAD

COUNT(Notification) as Complaints,

date

Resident date group by date;

DROP Table date;

Re: How to replace null for 0 value in script

CarlosAMonroy — Mon, 28 Mar 2016 15:03:34 GMT

Hi Cornelis,

It does not seem to be a Null value. Did you try?

if(Complaints='-' or isNull(Complaints),0, Complaints) as Complaints

Hope it helps!

Thanks,

Carlos

Re: How to replace null for 0 value in script

Mon, 28 Mar 2016 15:11:06 GMT

Have you try with

IF(len(Complaints)>=1 , Complaints, 0) as Complaints

Re: How to replace null for 0 value in script

Mon, 28 Mar 2016 15:21:36 GMT

Hello Avinash,

Sorry but not the solution:

The script cannot find the table.

Any idea?

Best regards,

Re: How to replace null for 0 value in script

Mon, 28 Mar 2016 15:24:47 GMT

Hello Ignacio,

Yes, already tried with this alternative, but not the solution

Best regards,

Cornelis

Re: How to replace null for 0 value in script

Mon, 28 Mar 2016 15:27:40 GMT

Dear Carlos,

Unfortunately not.

Initiall, I( thought it is not Null, biut the solution that you have provided me to take account for Null or '-' sign does not help.

Best regards,

Re: How to replace null for 0 value in script

Mon, 28 Mar 2016 15:40:21 GMT

Dear suman,

Finally, the solution.

The ApplyMap is something new for me.

notification:
LOAD * INLINE
[
Notification, date
123, 12-03-2016
134, 12-03-2016
765, 15-03-2016
]
;
test:
LOAD
COUNT(Notification) as Complaints,
date
Resident notification group by date;

DROP Table notification;

Test2:
Mapping LOAD
date,
Complaints
Resident test;

date:
LOAD * INLINE
[
date
12-03-2016
19-03-2016
15-03-2016
]
;

LOAD *,ApplyMap('Test2',date,0) as Complaints
Resident date;

Perfect and thank you very much for your kind support.

Best regards,

Cornelis

Re: How to replace null for 0 value in script

Mon, 28 Mar 2016 15:42:40 GMT

Hi Cornelis,

Solution given by Suman is correct and it add one level of computing to the script ie Applymap.. You can get your desire result simply using joining Date and Notification Table and the using Count.. Please see below script

Date:

LOAD * INLINE

[

date

12-03-2016

19-03-2016

15-03-2016

]

;

LEFT JOIN

notification:

LOAD * INLINE

[

Notification, date

123, 12-03-2016

134, 12-03-2016

765, 15-03-2016

]

;

test:

LOAD

COUNT(Notification) as Complaints,

date

Resident

Date

group by date;

DROP Table

Date

;