01-10-2018 10:39 AM - edited 07-23-2021 09:14 AM
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.
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):
Both the top and bottom tracks start with many significant digits in Fixed Decimal fields:
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:
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.
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!
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..
Hi @FlorianH @DivyaMadam
IBAN might be a situation that requires new functionality in Alteryx. I recommend that you do two things:
@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.
@lepome I fixed the modulo 97 calculation using a Python-Tool in the workflow. That worked pretty well.