Need to summarize fields that not always have a value

[detlefschneide1]

In my log (OS X System Log) I collect as transactions the time machine backup data.
I need to summarize the amount of backup that in the log is either listed as KB, MB or GB.

I created three rex, that fill me three fields with the amount of KB, MB and KB.

Now, I try to calculate these three field into a summary fields with

eval TM_Amount_Summary=TM_Amount_K/1024+TM_Amount_M+TM_Amount_G*1024

This works, as long as all three fields contain a value. Though, if for instance TM_Amount_G was not found in a particular transaction, the resulting TM_Amount_Summary remains empty as well.

The eval should work if one or two of the three fields would be empty. How can this be solved?

[detlefschneide1]

Found an additional way myself too:

eval TM_Amount_Sum=if(isnotnull(TM_Amount_K),TM_Amount_K/1024,"") | eval TM_Amount_Sum=if(isnotnull(TM_Amount_M),TM_Amount_Sum+TM_Amount_M,TM_Amount_Sum) | eval TM_Amount_Sum=if(isnotnull(TM_Amount_G),TM_Amount_Sum+TM_Amount_G*1024,TM_Amount_Sum)

It seems like a field can be overwritten by itself plus a calculation.

[martin_mueller · Accepted solution]

You could replace every field with coalesce(field, 0) to get around null fields in maths.

[gkanapathy]

This is actually the purpose of the coalesce() function, and it's probably a bit easier to read as well.

[detlefschneide1]

Found an additional way myself:

eval TM_Amount_Sum=if(isnotnull(TM_Amount_K),TM_Amount_K/1024,"") | eval TM_Amount_Sum=if(isnotnull(TM_Amount_M),TM_Amount_Sum+TM_Amount_M,TM_Amount_Sum) | eval TM_Amount_Sum=if(isnotnull(TM_Amount_G),TM_Amount_Sum+TM_Amount_G*1024,TM_Amount_Sum)

It seems like a field can be overwritten by itself plus a calculation.