Skip to main content
Announcements
Get Ready. A New Qlik Learning Experience is Coming February 17! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to replace null for 0 value in script

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

1 Solution

Accepted Solutions
dusasuman
Creator
Creator

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

Regards

Suman

View solution in original post

18 Replies
Anonymous
Not applicable
Author

Try :

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

Chanty4u
MVP
MVP

set nullvalue= ' ';

swuehl
MVP
MVP

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
MVP
MVP

or

load,

.......

  1. Where not IsNull(YourField); 

  2. or

LOAD ,

          Fields

FROM ...

WHERE LEN(TRIM(Field));

tyagishaila
Specialist
Specialist

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
Creator III
Creator III

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

Not applicable
Author

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;