topic Re: Change null values to "0" in QlikView

Change null values to "0"

Tue, 26 Nov 2013 09:50:09 GMT

Hi Guys

I need to change null values "-" to "0". How can i do this?

Where "Css_Total" and "Cyl_Total" is from different tables, linked by the common field "Date".

Can anybody suggest a solution to this problem?

Thanks

Re: Change null values to "0"

Tue, 26 Nov 2013 09:56:18 GMT

Hi,

You can try replacing the Null symbol with '0' instead of '-'. For this, go to Properties--Presentation tab and in bottom left corner, you can change it:

Hope this helps!

Re: Change null values to "0"

Tue, 26 Nov 2013 09:59:20 GMT

there are two ways

1) in chart--> presentation tab -->Null symbol--> write 0

2) if(css_Total='-',0,css_Total)

Re: Change null values to "0"

Anonymous — Tue, 26 Nov 2013 10:03:41 GMT

yes Shyamal you are right !! it works thanks.

Re: Change null values to "0"

Tue, 26 Nov 2013 10:07:50 GMT

This can also be done via a formula, specifically using ALT, as per my example below;

ALT( Sum(Value)/Sum(Orders), 0 )

ALT basically evaluates the first formula and if it does not come back with a legitimate answer then it will simply calculate the next formula instead.

Therefore, in my example, should Sum(Orders) = 0 for some reason, then Sum(Value)/Sum(Orders) will return NULL. The ALT function will therefore result in the second formula being used, in this case '0'.

I hope this is of some help - or at least enlightening.

Regards

Steve

Re: Change null values to "0"

Tue, 26 Nov 2013 10:19:41 GMT

I tried both methods. The first its working, but the value is not a number that i can manipulate. (I mean, i need to consider all data as numbers).

See the image below (strings on left and numbers on right side):

The "NullField" is a test that i did:

NULLASVALUE *;

SET NullValue ='0' ;

LOAD

null() AS NullField,

...

So, the conclusion until now is.. this null in the table is different from the Null().. i think that is something like "missing data". Any Idea?

Thanks

Re: Change null values to "0"

Tue, 26 Nov 2013 11:26:32 GMT

Thank you Stephen. But basically ALT works just for Null() values in the fields. My problem is missing data.

For example: In some months, doesn't exists any values, so.. this null is different from the Null() value in datafields.

Any other suggestions?

Thanks

Re: Change null values to "0"

Tue, 26 Nov 2013 11:32:41 GMT

try to use your expression inside num() ,

like this

num(expression field)

then display all in number

Re: Change null values to "0"

Tue, 26 Nov 2013 11:47:41 GMT

Thank you, but itn't work yet.

I think this would be a good solution for Null() in data fields, but my problem is null caused by missing data.

Any other suggestion?

Re: Change null values to "0"

karolina_ — Tue, 26 Nov 2013 12:28:19 GMT

editted to add: to have 0 for missing data you can add in reload script
if(Len([FieldName_])>0,[FiledName_],'0') AS [FieldName]

In the presentation tab you can set null symbol and missing symbol (not sure when it works so I added above for load script)
You should also change Aligment for Data (Text) to be the same as Data (numeric)

Re: Change null values to "0"

Tue, 26 Nov 2013 15:21:54 GMT

Thank you Karolina, great explanation. But this didn't work for me, because of the missing data and because i must consider this values to calculate the average on Last 12 months.

I'm studing to solve this using VBScript.

Thanks

Re: Change null values to "0"

mphekin12 — Tue, 26 Nov 2013 15:30:24 GMT

You could try something like this in the LOAD script:

if((isnull(Css_Total) or Css_Total=''), 0, Css_Total) as Css_Total

Re: Change null values to "0"

Wed, 04 Dec 2013 12:45:02 GMT

Thanks, problem solved.

Re: Change null values to "0"

Anonymous — Fri, 06 Dec 2013 05:34:02 GMT

why marked yourself as correct? mark that person answer correct who helped you..

Re: Change null values to "0"

Fri, 06 Dec 2013 10:31:42 GMT

Hi Anant

My mistake, sorry for that.

Thank you

Re: Change null values to "0"

Anonymous — Fri, 06 Dec 2013 11:56:42 GMT