Summary tool changing the data incorrectly

[sakinazarif]

Hi guys,

I have this weird issue in my workflow where the summary tool is changing the closing balance in my data incorrectly. I have attached the input and output excel sheets - my issue is with Org Unit 74203 ccy JPY balance going from -11million to -11. Any help will be appreciated - I am totally lost here, dont know what I'm doing wrong.

[Erin]

Hi @sakinazarif,
What version of Alteryx are you using? I see from the screenshot that Value is a V_WString, and I didn't think the Summarize tool would let you sum a string data type (mine doesn't at least, I'm on 2024.2).

I suspect this has to do with how Alteryx is converting the string values to numeric values. I played around with just Org Unit 74203 from your example and was able to convert Org Unit 74203 ccy JPY correctly. However, the summarize tool is off on the cents vs Excel. This might not be full proof depending on what this is going to be used for, but maybe a start? I played around with rounding and Fixed Deci vs Double data types. 

[davidskaife]

Hi @sakinazarif 

I'm unable to replicate the issue as it comes into my Alteryx version (2025.1) as a Double, As @Erin says your numerical field is actually showing as a String data type and what it appears to be doing is truncating some of the values when they are larger than millions. See below screenshot. 72076 and 75527 come through as the right value, but 74190 and 74203 don't - they come through as -129 and -11 respectively.

[sakinazarif]

Hi Erin,

I'm using the 2022 version

[sakinazarif]

Thanks David, weird but this was working fine until last week..I started facing this issue since this week tbh

[Erin · Accepted solution]

Interesting. I would recommend special attention when you convert this from a string to a numeric value before your summarize tool. @davidskaife's post reminded me that the default for Fixed Decimal data types is 19.6, but you can change this if you want to something higher. However, regardless the numeric data type you pick, the Summarize tool is going to optimize the best data type when doing aggregations. The sum will probably turn into a Double data type coming out of that tool (in my experience).  You might need to play around with it a bit.

I once had a situation where an amount was coming through in the quintillions (it was an obscure OCR issue that read an account as an amount). That was a fun one to fix! I was able to convert the amount correctly, but then certain tools couldn't handle the extreme length of the number. It was an audit nightmare. Very triggering. Let's not talk about it 😂

[jrgo · Accepted solution]

We are likely unable to reproduce the problem because Excel has likely done its thing and converted the Value to numeric and is why it reads in fine when we try. I assume that @sakinazarif your reading in from the source system, which is likely set the data type for the Value column with a format unsupported by Alteryx resulting in Alteryx defaulting it to string.

If the case, the issue is that some of the values contains non-convertible (to numeric) characters. So if you use a Select tool to change that column to Double, for example, most of the values will get NULL'd or return 0 depending how it was converted. Below is an example of what I'm referring to. 

Here's the expression I used to remove the bad characters and then convert. the IF condition is to add a - (negative) to the start if the original value started with a "(". The REGEX removes any character that is NOT a number or a "." (period/decimal).

TONUMBER(
IF STARTSWITH([Value], '(') THEN '-' ELSE '' ENDIF
+
REGEX_REPLACE([Value], '[^0-9.]', '')
)

[apathetichell]

Turn off amp --- see how the field types are set in your summarize tool. check output.

[sakinazarif]

You're right, alteryx gives conversion errors when running the summary tool

[sakinazarif]

This solution worked and my workflow looks fine now. Thank you so much for the help!!