ToString fails to covert Int64 as expected

[martinogorman]

I ran into a hard to find bug and struggled to understand why Alteryx was behaving in such an unexpected way. I have an Int64 integer Id with 19 digits ( but less than max Int64 - ie supported by Int64) . I want to use the ID as part of a string so decide to convert to a string but both ToString(Id) and ToString(Id,0) do not give me the result I expect. For example,

On the other hand, if I convert the Int64 to a string in a Select widget then the conversion works  fine 

I then noticed a couple of conversion errors - ConvError: Formula (4): Id: 1234567890123456789 does not fit in the type Double. So I guess the Int64 is converted to a double before being converted to a string. I understood that a double is in the range +/- 1.7 x 10-308 to 1.7 x 10308 . So I am confused as to why the Int64 doesn't fit in type Double as the conversion error indicates.

Can someone explain what is going on here? (update - having looked on StackOverflow I think I understand - 2^53 is the largest integer that a double can store without losing percision)

Personally, I don't think it is good practice to change variable types in Select widgets. And its also odd that changing to type String in the Select widget gives a different result to the ToString function. Would be nice to have consistent conversion behaviour in Alteryx. 

Martin

[danilang]

Hi @martinogorman 

This appears to be a bigger problem than you first suspected.  It appears that all numeric values are coerced into doubles on entry into the Formula tool.

Here's the input that I'm running tests with

Here's the config of the formula tool

Notice the last Formula.  All I'm doing here is copying the value to a new field.  No conversion.

Here are the results, all consistently converted with errors

And here are the conversion errors from the workflow.

All the incoming values are converted to double, including the straight copy.

Given the importance of the Formula tool in most workflows, I'm going to reach out to some moderators to see how we should proceed with this.  

Hi @SophiaF , @JessicaS.  @martinogorman has come across a major issue dealing with the way the Formula tool handles large integers.   How do we get this raised with Alteryx tech support to be addressed.

Dan

[martinogorman]

Thanks for following this up @danilang. I did see in a post from @jdunkerley79 (can't remember which one) when trying to work out what the problem was that fields are converted to doubles in the Formula tools. Obviously this is going to result in a problem for Int64. I guess its a legacy thing but definitely needs to be addressed. 

[danilang]

Hi @martinogorman 

I've opened a support case(Left of the page at the bottom. Who knew?) I'll keep you posted if I hear anything.

Dan

[danilang · Accepted solution]

Hi @martinogorman 

I have an answer from Alteryx...but it's not pretty

This post details the situation.  Specifically this message

The post then goes on to echo Bill Gate's famous, "why would anyone need more than 640K Ram", with the following line  "I guess the question I have is what are the int64 numbers that you have and what calculations do no need to do on them to greater than 15 digits of accuracy?"

Oh well.  Other than lobby them to implement native large int calculation in the Formula tool, there's not much we can do. 

In your particular case, changing the field type to string in a Select tool works, no matter how inelegant it may be.  Another alternative if you need to actually perform calculations on large ints is to use the R or Python tools and take advantage of the build-in support in these languages

Dan

[harsh_alang]

Starting version 23.1 Alteryx has added INT64 support in Formulas. Users will now be able to leverage large numbers in their calculations without losing precision.