topic How to convert null values into 0 in QlikView

How to convert null values into 0

Mon, 20 Jun 2011 18:11:10 GMT

Hi experts,

My source is an excel file.

I want to convert null values ( - ) into 0

I've tryed this but it doesn't work :

IF(ISNULL(ItemName)=0, ItemName, '0' ) AS ItemName

How to convert null values into 0

nagaiank — Mon, 20 Jun 2011 18:22:16 GMT

Is this a duplicate post? For the other post, I had replied as follows:

Try this instead.

If (IsNull(ItemName), 0, ItemName)

How to convert null values into 0

Mon, 20 Jun 2011 21:34:23 GMT

0 is false, -1 is true so try

if(isNull(ItemName)=-1,ItemName,0) as 'ItemName'

I don't actually know if this will do what you want but that could be the problem.

Also try using if(len(ItemName)=0,0,ItemName) as 'ItemName'

I've heard using len is better than isNull but off the top of my head I'm not sure why. Might be b/c len catches missing as well as null? Or maybe it's faster? I don't know.

How to convert null values into 0

Anonymous — Tue, 21 Jun 2011 03:44:48 GMT

If my memory serves me correctly there used to be a difference between what values were caught by Isnull() depending on if the QV instance was 32 or 64-bit. However, I'm pretty sure that was subsequently changed so that they function the same way. Anyway, I moved over to Len(Fieldname)=0 and haven't looked back.