Calculations with Field Type Int64

[mathias_schnoor]

Hey!

When I worked with large number from my postgres table than the greates bigint (int 64 too) is 8999999999999999999

for numbers greater as bigint I can use type numeric.

When I make a Int64 field with the value 8999999999999999999 Alteryx returned 9000000000000000000

Other example:

Postgres binint value 1234567891012345678

Alteryx returned the Int64 value 1234567891012345600

what is the matter?

Exists a Type like postgres numeric in Alteryx?

Mathias

[s_pichaipillai]

@mathias_schnoor

i don't have postgres table for testing 

but when i tested with your values it did return correct value.

can you convert to string then use select tool then convertback to INT64.

what version of alteryx are you using ?

[mathias_schnoor]

I use version 10.1.6.60263

connect a Formula Tool between your Select Tool and your Browse Tool.

See attachment

[s_pichaipillai]

@mathias_schnoor 

i see what you are trying to do . 

why do you need to use formula again to convert to INT64 as the select already converted to same data type?

However this is really interesting :)

@TaraM, Mathis found something interesting

1. converts the bigint number from string to INT64 using select tool

2.he create the same copy of the data using formula tool but data errored :(

can you help ?

Thanks

Saravanan

[JohnJPS]

I believe you can used "FixedDecimal" for large number values. .... EDIT: then again maybe not: I get the same data error when using FixedDecimal.

[TaraM]

Thanks @s_pichaipillai - we'll look into it.

[AdamR_AYX]

The problem is unfortunately the Alteryx formula tool supports a smaller set of datatypes than the full Alteryx set.

Namely: String, Double and Spatial

Everything gets converted to one of these three types as it goes into an Alteryx formula and then the result is converted back to the requested type after the formula has been caulculated.

In your sample module you will notice you are getting field conversion errors telling you that your numbers do not fit in a double

[mathias_schnoor]

You get the same warning when you convert the same bigint int double or fixdecimal.

[AdamR_AYX]

That is correct.  So all numeric types get converted to a double as they enter a formula which generates the 6 field conversion errors about not fitting in a double in your log (2 rows of data x 3 formula tools).

Then the formulas are calcualated (in this particular example they just pass the values straight through)

Then the results of the formula (in this case doubles) are converted to the type selected in the formula tool.  So in this particular example this generates a second pair of field conversion errors as the answers do not fit in a fixdecimal 19.6.  These 2 are easily fixed by increasing the fixed decimal size to 26.6 which is big enough to hold the answers in this case.

I guess the question I have is what are the int64 numbers that you have and what calculations do no need to do on them to greater than 15 digits of accuracy?  Then we can see if we can come up with a work around for you.

[mathias_schnoor]

@AdamR_AYX thank you for your support.

We have no current calculation to use big int greater than 15 digits.

For our analyses we use postgres in the past. Postgres has the type nummeric, Nummeric is greater than 15 digits.

When we need floating-point numbers greater than 15 digits we get the type nummeric.

This is our approach and motivation, to make an example in Alteryx.

Mathias