Handling Unusually Large Numbers in Alteryx Designer

[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

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

   

Stream 2: Negative set of numbers

1. Add recordID starting with 0.
2. Using the Formula tool, append “–“ in front of the “large number.” This will be a string operation.
   
   

   

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

Difference of Large Numbers.yxmc

[BS_THE_ANALYST]

@Gaurav_Dhama_ I did not know:

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.

That's wild! I really wouldn't expect that behaviour from a Fixed Decimal!? I thought the whole point of this was that we can maintain higher precision, it's weird to think this is lost if we then use it for calculations. Effectively, if I took my fixed decimal and added 1 to it, it could alter the decimal value 😆?

[Gaurav_Dhama_]

Hi @BS_THE_ANALYST,

It actually does that.

Below is the snip of the same, I used 40.5 as the precision value. Which should ideally be able to handle the number I am passing.

Before adding 1:

After Adding 1:

[BS_THE_ANALYST]

@Gaurav_Dhama_, this is very interesting! Every day is a school day! Thanks for providing your solution to work around these types of constraints 👏. It's been nice seeing how engaged you've been with the community lately, thanks a bunch 👍.

[Dhrish]

Hi @Gaurav_Dhama_ so I have a similar issue. I am getting the values as double since it has a decimal, I tried subtracting them keeping the double and the output that I get in double is weird something like this 1.00000000093132e-02 when I should have got 0.01. So, what I did is that I made the output expression to be a fixed decimal. This gives me the correct answer. Just wanted to check if this is the right approach or should I do what you have explained?

[Pilsner]

This is very interesting, thank you @Gaurav_Dhama_. I've just completed weekly challenge 284, which deals with the division of large numbers. I know the summarise tool isn't able to directly compute division (you could do some generate rows + average logic, but it's not a prebuilt function), but it got me thinking, does this same idea work for the "Product" function?

Interestingly, in this case, the output is returned as a double, so the output here is similar to that represented in a formula tool. I wonder if the product function effectively creates one long formula under the hood 😄. 

To get around this, I suppose you could use a generate rows, then a sum to calculate the product too. 

Input: (I want to do [Field1] * [Field2])

Output:

I'm still curious if this fixed decimal metadata persists with any other summarise functions. 

Either way, your solution for handling large numbers is very impressive and a great spot. If I hadn't read this post, I would have had to create some horrible substring logic to help with problems like these.  

[Gaurav_Dhama_]

@Dhrish how are you taking the difference? try using fixed decimal in your case as 1.00000000093132e-02 is actually 0.01. You are getting that large value due to high decimal precision. Do your numbers contain large decimal values? Try converting it into fixed decimal with 12 scale and 20 prcision.

Alternatively, to use the above approach with decimal check out the attach workflow.

If you need to maintain that decimal value, say 8-10 decimal values e.g. 21.123456789012 and you want to retain these values, I will remove the decimal value and maintain a divider, in this case that will be 100000000000 and change the number to 21123456789012. then take the difference and divide the result with this divider.

Result

Validation with formula tool:

So now you can get decimal precision as well.

[Dhrish]

Hi @Gaurav_Dhama_ So I have the data like this which are double type

and then I do the following difference

 you see, when i set the output diff as double it gives me the weird ans but if I keep it as fixed decimal it is correct. Just wanted to check if this approach is good.

[Gaurav_Dhama_]

This is common among most programming languages as well, because of how computers store values. Ideally a number needs to be representable in binary, which becomes difficult for some decimal places, hence we see a very weird number like 1.00000000093132e-02, however if you lay it down normally it is nothing but 0.0100000000093132 which is very close to 0.1.

So, in short, it is very common to see a scientific number as a result of decimal differences. But your output anchor should ideally still show it in the format that you have selected. Right now, you are looking at the formula result preview. This is only to help you write the formula, but the output you will get once you run it will be a proper difference even in double, i.e., 0.01.