Null Values

[tmumaw]

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

[disqr_rm]

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

[tmumaw]

Rakesh,

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

Thanks
Thom

[disqr_rm · Accepted solution]

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

[tmumaw]

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;

[tmumaw]

Fixed.....Thanks for your help.

Thom

[disqr_rm]

Try:

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

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;

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?

[amien]

Just press F1 on NullAsValue

Tip of the day Rakesh .. thanks 🙂