This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.

Big numbers and Alteryx

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 number data 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 50 digits. 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 for designatingbig 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 be performed on Strings.

Most numeric values (things you would actually perform mathematical operations on) have only a few significant digits.

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. They are really versatile--they can represent staggeringly small or large numbers. (10-308 to 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 your identifiersas 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 is a 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 precision degrades 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.