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.
SOLVED

Use very large numbers within Alteryx

ulrich_schumann
8 - Asteroid

Hi all,

 

I did some calculations based on the 'chessboard-wheat' problem.

Here an extract out of Wikipedia:

The wheat and chessboard problem (sometimes expressed in terms of rice grains) is a mathematical problem expressed in textual form as:

If a chessboard were to have wheat placed upon each square such that one grain were placed on the first square, two on the second, four on the third, and so on (doubling the number of grains on each subsequent square), how many grains of wheat would be on the chessboard at the finish?

The problem may be solved using simple addition. With 64 squares on a chessboard, if the number of grains doubles on successive squares, then the sum of grains on all 64 squares is: 1 + 2 + 4 + 8 + ... and so forth for the 64 squares. The total number of grains equals 18,446,744,073,709,551,615, much higher than what most intuitively expect.

 

When I run my workflow, everything is fine until field 63. On the last field, the number seem to be too big and cannot displayed properly (becoming a negative number).

 

What is the limitation for numbers in Alteryx? Is there any workaround?

 

10 REPLIES 10
jdunkerley79
ACE Emeritus
ACE Emeritus

Alteryx integers are at most 64 bit, i. e. they max out at 2^63 - 1 (your target is 2^64 - 1)

 

If however you use a decimal type and specify the width as 32 and the scale as 0 then it will hold sufficient digits for your calculation.

 

Sample attached

AdamR_AYX
Alteryx Alumni (Retired)

Inside a formula tool all numbers are converted to doubles, and the calculations are done as doubles, and then converted back to the field output type.

 

So you are hitting the limits of what will fit in a double int64. (Actually James is correct above, you are hitting the limits of the Int64 as it is converted back)

 

I can't think of a workaround off the top of my head.  I'm afraid Alteryx isn't really the tool for solving this type of problem.

 

Edit: Jame's work around works in this case

Adam Riley
https://www.linkedin.com/in/adriley/
ulrich_schumann
8 - Asteroid

@jdunkerley79: thanks for the solution & explanation. In your example field 64 already covers the total sum instead of the number of wheat at field 64 (the table would need to start with "1" in field 1). But no need for modification from my side.

Your input is very welcome!

Bob_Blackey
11 - Bolide

Just to throw it out there ...

 

I faintly remember in high school doing a coding exercise to multiply large numbers using strings. You could multiply numbers of any size (up to the limit of a string) which went beyond the limits of the time (Numbers were smaller then  :-)    ). I wouldn't even remember where to start  ( but I'm guessing an iterative macro would have to be involved). But it is possible.

 

joshbennett
11 - Bolide
11 - Bolide

@Bob_Blackey You just had to go and say that Bob, didn't you? I was supposed to be getting my to-do's done and then you had to go "throwing stuff out there".

 

...Okay so I looked into it and apparently this is quite possible, as you suggested. This page gives a nice explanation and even the code of a few simple example programs that enable this calculation in C++, Java, C#, and most interestingly, Python: https://www.geeksforgeeks.org/multiply-large-numbers-represented-as-strings/

 

So, I dropped the example code into the Python tool in Alteryx and it worked right out of the box!

 

Thus, @AdamR - I respectfully disagree. Alteryx IS the tool for solving this problem, just with a little tag team help from its good pal Python. Together, these two form the almighty Pytheryx!

 

🙂

 

FlorianH
7 - Meteor

Hi @joshbennett,

 

I'm not familiar with macros or phyton in Alteryx and I wonder, if that might be solutions for this problem:

 

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. The reason for this miscalculation seems to be, that the Formula-Tool can not handle numbers, that are as large as the first one. Do you have any idea for a workaround for me?

 

FlorianH_0-1625752207841.png

 

 

 

Thanks a lot in advance!

harsh_alang
Alteryx
Alteryx

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.

HarshA
ChrisK
Alteryx
Alteryx

Unfortunately, your example (mod(700901001234567890131400,97)) needs numbers much larger than Int64, so even version 23.1 won't be able to compute that result exactly.

FlorianH
7 - Meteor

Thank you so much for the update on that issue. I appreciate that very much!

Fortunately, I was able to build a workaround using the Python-Tool in Alteryx. It worked very well for me!

 

Thanks and greetings,

 

Florian

Labels