Change null values to "0"

[#1]

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

[#2]

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!

[#3]

there are two ways

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

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

[Anonymous]

yes Shyamal you are right !! it works thanks.

[#5 · Accepted solution]

Hi

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

[#6]

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

[#7]

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

[#8]

hi

try to use your expression inside num() ,

like this

num(expression field)

then display all in number

[#9]

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?

[karolina_]

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)