Engine Works

Gaurav_Dhama_

Alteryx allows users to perform calculations that seem impossible traditionally. But there always comes a point where we hit a dead end. One such case is large numbers.

So, how do we work with large numbers in Alteryx, without using Python or any other complicated workaround?

Understanding the Problem

Alteryx allows multiple number formats, such as int16, int32, int64, double, fixed decimal, float, byte and bool. But these formats are visual, meaning this is how you see these numbers, and what values you want to retain.

Any kind of calculation that you need to perform using the Formula, Multi Row Formula, or other tools with a formula expression editor uses double as the format in the backend to perform the calculations.

So, if you have a number that is 20 digits, you can make it fixed decimal with 50.0 (max value) precision, and you will see the whole number correctly. BUT, once you perform any kind of operation on it, it loses its precision, and you get a vague value. This happens because to perform the calculation, Alteryx converts to a double in the backend and then performs the calculation, losing the precision that you had defined.

Solution

Ok, now that we understand the problem, let’s solve it. But wait, Alteryx uses only double, then how do we perform any calculations?

That is where the Summarize tool stands out!

It does not follow the standard method of converting Fixed Decimals to Double for calculation, but rather maintains the precision.

Now that we have identified the wolf among sheep. Let us see how to use it.

Method

I am going to demonstrate how you can take the difference of two large numbers; you can perform the other kinds of operations using the same concept.

Below is a screenshot of the workflow for your reference, as we walk through the steps.

Step 1: Preparing Inputs

We import the numbers as strings to maintain precision. We do not want to rely on any number format for now.

Below are two sets of large numbers that I want to subtract.

Before we proceed, I will add “RecordID” and call it RowPosition because we want to use this ID for referencing these values later in the workflow.

Then, using the Transpose tool, I bring all the numbers into a single column.

Step 2: Converting the Number Signs

For difference, we need two sets of numbers, positive and negative. Why? Because the Summarize tool does not allow a difference operation.

We do this by taking two streams out, lets call them stream 1 (Positive set of numbers) and stream 2 (Negative set of numbers).

Before that, we will sort the data into ascending order of RowPosition.

Stream 1: Positive set of numbers

Add a recordID starting with 1.
Add a Select tool and convert it into Fixed Decimal 50.0 precision.

Stream 2: Negative set of numbers

Add recordID starting with 0.
Using the Formula tool, append “–“ in front of the “large number.” This will be a string operation.
Add a select tool and convert it into Fixed Decimal 50.0 precision

Step 3: Taking the difference

Union the two streams together, so that we have both a positive set and a negative set of numbers together.

Now this is where magic happens, add a summarize tool and Group By “RowPosition” and “RecordID,” sum over “Large Number.”

At this stage, you will get your differences and the original set of numbers as output.

Step 4: Refining the output

Let us join the output of Summarize with the output of Union like this:

We will try to match the values that already exist before our “Summarize.”

Honestly, the “Join” anchor output of this Join is irrelevant for us. What we are interested in is the “Left” anchor output, because that will give us the actual differences.

Now we have the position and the difference, we will bring back the original data using Join, joining on RowPosition.

Sort the data and unselect the unnecessary columns, and we have our result below.

Summary

You can perform calculations on large numbers using Alteryx-native tools just by performing the calculation in a bit of an untraditional manner.

Bonus Note: The product of two “large numbers” will mostly give you a scientific number, because a number that big will hold no meaning in smaller figures.

It is the same as saying light speed is 3*10^8 m/s while it is 299,792,458 m/s.