How to replace null for 0 value in script

[#1]

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

[dusasuman · Accepted solution]

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

Regards

Suman

[#3]

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;

[CarlosAMonroy]

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

[#5]

Have you try with

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

[#6]

Hello Avinash,

Sorry but not the solution:

The script cannot find the table.

Any idea?

Best regards,

[#7]

Hello Ignacio,

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

Best regards,

Cornelis

[#8]

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,

[#9]

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

[#10]

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

;