topic Null Values in QlikView

Null Values

tmumaw — Tue, 09 Feb 2010 15:47:01 GMT

I have added the following logic to my script to check for null values. It works just fine until I create a report. I used the following to sum up contracts not started : sum( if ( status = 'NS', [Total Sales], 0)) Nothing happens it's all zeros.

STATUS:
LEFT JOIN (VBAK)
LOAD Status,
[Status Desc]
FROM (ansi, txt, delimiter is ',', embedded labels, msq);

NullMap:
mapping LOAD
null(), 'NS'
AUTOGENERATE 1;

map Status using NullMap;
right join (VBAK)
LOAD DISTINCT
*
RESIDENT VBAK;

Thanks
Thom

Null Values

disqr_rm — Tue, 09 Feb 2010 15:51:30 GMT

Check QV help documentation for NullAsValue. This should give you what you need.

Null Values

tmumaw — Tue, 09 Feb 2010 16:06:54 GMT

Rakesh,

I am new to qlikview (6mths). Where exactly should the NullsAsValue go?

Thanks
Thom

Null Values

disqr_rm — Tue, 09 Feb 2010 16:16:40 GMT

In the begining before you load the data. Just press F1 on NullAsValue and you will se a good documentation on it.

Null Values

tmumaw — Tue, 09 Feb 2010 16:46:37 GMT

Rakesh,

I have tried placing that stmt everywhere with no luck. Here is where I put it the last time. And this is the calculation I use : Sum(if(Status = 'NS', [Total Sales], 0))

ZTRPST:
LEFT JOIN (VBAK)
LOAD %ContractItemKey,
Status,
Date,
date(floor(weekend(Date, 0, -1)),'YYYY-MM-DD') as WEDAT,
num(floor(weekend(Date, 0, -1))) as numdatekey,
sum(Hours) as [Hrs Worked]
FROM (qvd)
group by %ContractItemKey, Status, Date;

STATUS:
NullAsValue Status; <----------
LEFT JOIN (VBAK)
LOAD Status,
[Status Desc]
FROM (ansi, txt, delimiter is ',', embedded labels, msq);

NullMap:
mapping LOAD
null(), 'NS'
AUTOGENERATE 1;

map Status using NullMap;
right join (VBAK)
LOAD DISTINCT
*
RESIDENT VBAK;

Null Values

tmumaw — Tue, 09 Feb 2010 16:52:07 GMT

Fixed.....Thanks for your help.

Thom

Null Values

disqr_rm — Tue, 09 Feb 2010 16:54:36 GMT

Try:

NullAsValue Status; <----------

LEFT JOIN (VBAK)
LOAD Status,
[Status Desc]
FROM (ansi, txt, delimiter is ',', embedded labels, msq);

Then Sum(if(Status = null(), [Total Sales], 0)) or Sum(if(isnull(), [Total Sales], 0)) should work.

If doesn't work, can you post some sample data?

Null Values

amien — Tue, 16 Feb 2010 10:08:51 GMT

Just press F1 on NullAsValue

Tip of the day Rakesh .. thanks 🙂