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 Knowledge Base

Definitive answers from Designer Desktop experts.

Big numbers and Alteryx

lepome
Alteryx Alumni (Retired)
Created

Every few months we get a flurry of cases in which users observe that very large numbers don't behave the way users expect. In most of these cases, large numbers serve to identify precise data. The classic example of this is account numbers such as credit card numbers. A typical credit card has 16 digits.

Problems arise when such numbers are introduced into Alteryx as numberdata types. Yes, an Int64 is designed to handle an integer with up to 19 significant figures, and a Fixed Decimal can nominally accommodate up to 50digits. Often, though, it's better practice to handle a big number like that as a String. That may be counter-intuitive, but there are very good reasons fordesignatingbig numbers as Strings.

  • The most relevant reason is that the number is being used as an identifier. Credit card numbers, for example, are never added together or multiplied. They aren't being usedin mathematical operations, so storing them as numbers is unnecessary. There are still many operations that can beperformed onStrings.
  • Most numeric values (things you would actually perform mathematical operations on) have only a few significantdigits.
  • Computers bog down when doing computations to very high precision, and speed is highly valued in computation. Alteryx is no exception to this. Almost all numeric computation carried out in Alteryx tools uses Double data types.(Doubles are double-precision floating point data types. Theyare really versatile--theycan represent staggeringly small orlargenumbers. (10-308to 10308), but are limited to about 15 significant digits.) And this is good enough for most applications, particularly when trade-offs with speed are considered.

If you store youridentifiersas numbers, and then apply tools that use Double data types, you have to do conversions, and this is where you run into peculiarities. Suddenly precision degrades. So store your identifiers as Strings, and they will retain all their digits.

But what if the numbers are that precise, and they really do need to be added together? There isa tool that can help in such situations: the Summarize Tool. Here's an example (attached in the v11.0 workflow Big Numbers in Alteryx.yxmd):

Big Numbers.png

Both the top and bottom tracks start with many significant digits in Fixed Decimal fields:

Text Input.png

The blue tools on the top track generally convert to Double types for processing, and then (in this case) output as Fixed Decimal. The Summarize Tool on the bottom track uses the full Fixed Decimal precision. If we look at the data output stacked, we can see that precisiondegrades in the top calculations, but is retained in the bottom:

Output.png

Only when precision falls to about 15 significant digits do the values converge. They're close in all cases, but only the same in Fields 7 & 8.

If you really need to do precise addition, you can. Just don't expect it to be as fast. And if you don't need to do precise addition, store your identifiers as Strings and save yourself a lot of confusion.

Attachments
Comments
FlorianH
7 - Meteor

Hi LisaL,

 

thanks for this interesting post! I have the following problem with big numbers:

 

in order to check IBANs, I want to calculate the modulo 97 of very large numbers. In my example the number is 700901001234567890131400. Two websites independently say, that the result of 700901001234567890131400 modulo 97 is 90 (e.g. https://www.entwicklertools.de/tools/mathematik-tools/modulo-rechner/). But when I calculate it with Alteryx the result is -79.

 

How can I handle this in Alteryx?

 

Thanks a lot in advance!

DivyaMadam
5 - Atom

Hi, 

 

I am facing challenge with IBAN spilt. I need to split IBAN into 3 sections. 1st 2 characters in 1 section, 3rd and 4th in section and remaining numbers in 3rd section. when I do that the output file has the 3rd section truncated...  please let me know if we have a solution for this..

lepome
Alteryx Alumni (Retired)

Hi @FlorianH @DivyaMadam 
IBAN might be a situation that requires new functionality in Alteryx.  I recommend that you do two things:

  1. post on the Ideas page:  Please look for this idea in the Ideas section of Community. If it has been suggested within the past couple of years, Like the post, and add your use case in the comments. If your idea is not recently listed, please submit it again. Our product team pays particular attention to ideas submitted by users.
  2. Ask your account team to submit a Voice of the Customer request on your behalf.
FlorianH
7 - Meteor

@DivyaMadam  Did you use the RegEx-Tool to make the split? And you should probably set the column with the remaining numbers to a string type. Then it shouldn't be truncated.

FlorianH
7 - Meteor

@lepome I fixed the modulo 97 calculation using a Python-Tool in the workflow. That worked pretty well.