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

[Anonymous]

Try :

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

[Chanty4u]

set nullvalue= ' ';

[swuehl]

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.

[Chanty4u]

or

load,

.......

1. Where not IsNull(YourField); 
2. 
   
3. or
4. 
   

LOAD ,

          Fields

FROM ...

WHERE LEN(TRIM(Field));

[tyagishaila]

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;

[Kushal_Chawda]

Take one more resident load to evaluate the condition

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 *
Resident test;

Drop Table test;

Test 3:

LOAD *

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

Resident Test2;

Drop Table Test2;

[veidlburkhard]

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

[#9]

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;

[avinashelite]

Try like 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,
if(isNull(Complaints), 0, Complaints) as Complaints
Resident test;

Result:

LOAD *,

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

Drop Table Test2;