Alteryx Designer Desktop Discussions

Steve314

I have recently found a bit of a quirk with the standard filter tool.

I was trying to filter out values equal to zero, but found that each time I ran the workflow some fields populated '0' were not being filtered out. Even more confusing was the fact that each time I ran the workflow I got a different number of records passing through, even though the data is identical each time.

So, it seems as though Alteryx is identifying different records as being non zero each time the workflow is run. This in turn affects ,calculations further down stream.

Luckily the solution is simple - change the data type from double to fixed decimal. Now the filter tool successfully removes all zero records every time the workflow is run.

The main reason for mentioning this is that many people will be unaware of this bug and knowing about it may save you a lot of time. I spent ages checking through the workflow to see why I was getting different results each time I ran it!

ChrisTX

This is not a bug, not limited to the Filter tool, and generally not "caused" by Alteryx.

The root cause is:

Floating-point numbers: how computers store numbers: they use base-2, and base-2 cannot represent "some" base-10 decimals. This leads to simple decimal numbers appearing differently or rounding differently than expected.
The only way to correct this is to stick with integer math, as all base-10 integers can be represented correctly in base-2.

Since this is an issue with all computers, Microsoft explains the issue for Excel users in the following article: https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-resu...

One of their examples: Enter the following into a new workbook:

A1: =(43.1-43.2)+1

Right-click cell A1, and then click Format Cells. On the Number tab, click Scientific under Category. Set the Decimal places to 15.

Instead of displaying 0.9, Excel displays 0.899999999999999. Because (43.1-43.2) is calculated first, -0.1 is stored temporarily and the error from storing -0.1 is introduced into the calculation.

The only way to correct this is to stick with integer math, as all base-10 integers can be represented correctly in base-2.

You will likely experience unique challenges when processing data type Double, or when using the Formula tool, or the ROUND function.

Alteryx converts all numbers to a Double when doing math operations and then converts them back to the original data type. This can result in a loss of precision due to how computers store floating point numbers.
The Alteryx "blue tools" (Preparation tools) convert everything to Double for processing.
The Alteryx formula tool supports a smaller set of datatypes than the full Alteryx set. Namely: String, Double and Spatial. Everything gets converted to one of these three types as it goes into an Alteryx formula and then the result is converted back to the requested type after the formula has been calculated.

Examples:

Round(1.25, 0.1) = 1.3 Correct: rounds UP as expected
Round(1.575, 0.01) = 1.58 Correct: rounds UP as expected
Round( 1.255, 0.01) = 1.25 Incorrect. Should round UP to 1.26

206.95 when stored as a double in binary is actually '206.94999999999998863131622783839702606201171875' , and when rounded at the first decimal place becomes 206.9

1.45 when stored as a double in binary is actually '1.4499999999999999555910790149937383830547332763671875' and when rounded at the first decimal place becomes 1.4

1.55 when stored as a double in binary is actually '1.5500000000000000444089209850062616169452667236328125' and when rounded at the first decimal place becomes 1.6

MyNumber	Formula Used	Result
4.717949	ROUND([MyNumber], 0.1)	4.7
4.35	ROUND([MyNumber], 0.1)	4.3
4.510204	ROUND([MyNumber], 0.1)	4.5
4.283757	ROUND([MyNumber], 0.1)	4.3

References (some of these may be archived):

I avoid data type Double, when possible.

Chris

Qiu

@ChrisTX
Thanks for the very infromative reply.

Alteryx Designer Desktop Discussions

Filter tool when numeric data field set to double