topic IsNull in QlikView

IsNull

tmumaw — Thu, 23 Sep 2010 20:35:07 GMT

Hello All,

I am having a problem summing some fields in my load script. The only time they sum correctly is if all fields (Sunday - Saturday) are populated with a value, otherwise all I get when I create a list box is '-' in the weeksum column. Thanks Thom

if(isnull([tblDrgOrders.sun]),0,[tblDrgOrders.sun]) + if(isnull([tblDrgOrders.mon]),0,[tblDrgOrders.mon]) +
if(isnull([tblDrgOrders.tue]),0,[tblDrgOrders.tue]) + if(isnull([tblDrgOrders.wed]),0,[tblDrgOrders.wed]) +
if(isnull([tblDrgOrders.thu]),0,[tblDrgOrders.thu]) + if(isnull([tblDrgOrders.fri]),0,[tblDrgOrders.fri]) +
if(isnull([tblDrgOrders.sat]),0,[tblDrgOrders.sat]) as WeekSum,

IsNull

Thu, 23 Sep 2010 20:45:41 GMT

Hi Thom use the rangesum function instead, it will also save the if validation. The expression might look something like this:

rangesum( [tblDrgOrders.sun],[tblDrgOrders.mon],[tblDrgOrders.tue],[tblDrgOrders.wed],[tblDrgOrders.thu],[tblDrgOrders.fri],[tblDrgOrders.sat] )

it might also be safer if you cast to num, in case you have empty strings instead of values, like this:

rangesum( num([tblDrgOrders.sun]), num([[tblDrgOrders.mon]), num([[tblDrgOrders.tue]), num([[tblDrgOrders.wed]), num([[tblDrgOrders.thu]), num([[tblDrgOrders.fri]), num([[tblDrgOrders.sat]) )

Regards

IsNull

tmumaw — Thu, 23 Sep 2010 22:36:26 GMT

Thanks Ivan,

That worked perfectly on summing up all the fields. The individual fields still show ' ', is there a way through the load script to replace the null values with '0'?

Thom

IsNull

Thu, 23 Sep 2010 22:47:03 GMT

Hi, use similar expressions for individual fields, like this:

rangesum( num([tblDrgOrders.sun]) )

Regards

IsNull

Fri, 24 Sep 2010 00:01:18 GMT

Just a word of caution using null tests in that 64 bit results are inconsistent - for this reason I always use a len test instead i.e if (len(fieldname) = 0,.........)

Regards,

Gordon

IsNull

tmumaw — Fri, 24 Sep 2010 13:17:13 GMT

Thanks to both of you. Your suggestions worked perfectly. I need to talk to our developers to be sure they initalize the fields correctly. This will make my life much easier. Have a geat day.

Thom