Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Increasing Precision for Fixed Decimal results in null values

bb213
8 - Asteroid

I am trying to increase precision on some calculations.  I'm looking to get as precise as possible.  The problem is as I increase from 19.6 to 50.48, I am getting all null values. What is the workaround for this issue? 

9 REPLIES 9
harikakummara
6 - Meteoroid

How are you trying to increase the precision? I did a work around using a "Select" tool --> Type "FixedDecimal" --> Size 50.48 for a test data. I see no issues.

Capture.JPG

bb213
8 - Asteroid

I don't think that will work - the precision needs to be part of the calculation in the formula tool.  Changing precision in a select tool will only have the effect of adding zeroes on to the end, which is not what I need. 

AdamR_AYX
Alteryx Alumni (Retired)

Do you have some more detail on your issue? The formula tool will handle all numbers as doubles so that sets some limits on precision.

Adam Riley
https://www.linkedin.com/in/adriley/
SFreiter
6 - Meteoroid

More details will be helpful with your solution as we are only guessing by your responses what your issue is.  Number showing as null would lead me to believe your data may have numbers pulled in with a data type of string.  You will may need to convert the column data type first.

 

Fixed poor grammar

pkasik
5 - Atom

I can confirm this behavior exists. Here is data as double:

pkasik_0-1685726707772.png

FixedDecimal 19.16 

pkasik_1-1685726745976.png

FixedDecimal 20.18

pkasik_2-1685726772435.png

FixedDecimal 24.12

pkasik_3-1685726811628.png

I simply retyped the column in Select tool. But problem exists even when I tried calculations (Formula) with high precision Fixed Decimal. It resulted in null.

I do not think that Fixed Decimal should work like this.

geraldo
13 - Pulsar

@pkasik

 

in cases that alteryx recognized from the fixededimal database i put the cast to varchar(50) in the station query of the database and in alteryx it recognizes as a double and brings all the precisions

pkasik
5 - Atom

I do not have problem in obtaining data from database. I have problem that Alteryx is losing data in fixed precision calculations. The data was double originally, I have no problem with that - but when I try to format output to fixed precision I lost part of the data and did not even noticed because I did not expected this could happened.

geraldo
13 - Pulsar

@pkasik 

 


This suggestion I due to having had a similar problem.
In the database and bigint format and alteryx changes the data type to fixeddecimal 17.2 and loses precision when the number exceeds 17 positions.
So I changed my query that searches the database casting a varchar for alteryx to receive the data with all the details.

apathetichell
18 - Pollux

@pkasik the issue in screengrab two is that your have more than 2 values in your integer column (ie a two digit number and a decimal). You need to make sure your fixed decimal lengths accommodate your values. I believe if you dig into the error/warning messages in your workflow you should see a message saying that the value didn't fit into your fixed decimal. This is not a standard truncating warning - it's telling you the value will be dropped resulting in the null.

 

I would caution that some of Alteryx's core math functions operate at a lower level of sensitivity than this and may truncate values. My hypothesis is that this is either behavior of the underlying C function - or it is how Alteryx is porting the data to the C function. I broke down and started using Python (where I bring numbers in as strings - and then have them leave as strings) to handle this level of sensitivity.

 

Alteryx isn't alone here - I see this kind of thing in other platforms as well (try select 1/14 as truncated; in snowflake and see the result).

Labels