MOD-formula gives unexpected result

[FlorianH]

Hi 🙂

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. What could be the reason for that? Is the first number to big for Alteryx to handle?

Thanks a lot in advance!

[apathetichell · Accepted solution]

I have a hypothesis - Mod is traditionally an integer function and your number is too large for Int64 so it's producing weird results...

support for hypothesis:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Modulo-mod-Function-Not-just-for-Ev...

specifically says "two integers"

when I do try to convert it to an integer I get:

Formula (2) test: 700901001234567940538368.000000 does not fit in the type Int64

perhaps you can run it in the python tool with fmod?

[Qiu]

@FlorianH 

The mod of large number appears to be a challenge for most of the coding tools, meaning can not be computed directly.

I prepared a simple macro by following the idea of this.

I need to improve it though since it will not work for small numbers due to the data type mismatch. 🤣

[Qiu]

@FlorianH 

The lower snapshot is trying to prove that Excel also fails to calculate the mod of large number.

[FlorianH]

Hi Qui,

thank you so much for the macro! And sorry for not coming back on this issue for so long time, but unfortunately I had some other priorities. 

Could you improve the macro?

And could you please explain in more detail what is happening in the macro?

Thanks so much!

Florian

[FlorianH]

Thanks for your input! I solved the issue by using a Phyton Module.

[apathetichell]

@FlorianH - I can't believe you found a solution to a problem you had three years ago  - and didn't need a reposting of information which was already contained in the thread (ie that Alteryx integer functions use Int64).

A bit sad that you didn't accept my solution - I mentioned you should use a python module and explained why.