community
cancel
Showing results for 
Search instead for 
Did you mean: 

Engine Works Blog

Under the hood of Alteryx: tips, tricks and how-tos.
Asteroid

It seems like a very straight forward thing to do - comparing numbers. It ends up much more complicated than people think. The problem is that 2 numbers that look the same might have be created via very slightly different mathematical functions and might differ only in the 16th decimal place. This is compounded by the fact that all floating point numbers are actually stored as base 2, not base 10.

 

In the past I have suggested to people that they write a formula like:

 

abs(a-b)<0.00001

 

That works for small numbers, but as numbers get very large (> 10^14 or so), that stops working. The reason it stops working is because a floating point number only stores about 17 significant digits of information. For a big number that 17th digit starts getting pretty big, even though for comparison it is trivial.

 

Alteryx 5.0 adds 2 new functions to the formula language: CompareEpsilon & CompareDigits

 

CompareEpsilon is exactly like the example above. You could rewrite it to:

 

CompareEpsilon(a,b,0.00001)

 

It is just a short cut to make what is being done more clear.

 

CompareDigits is the more useful function. CompareDigits(a,b,10) will return true if the 1st 10 significant digits of a and b are the same. That works equally well for comparing 1.7==1.7 as 1.7e50==1.7e50. CompareDigits is definitely the preferred way of comparing two numbers that may be anything other than integers.

Labels