Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
See the attached qlikview file. Resolved using applymap() function.
Regards
Suman
Try :
alt ( COUNT(Notification) , 0 ) as Complaints,
set nullvalue= ' ';
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.
or
load,
.......
LOAD ,
Fields
FROM ...
WHERE LEN(TRIM(Field));
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;
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;
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
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;
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;