community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.

ConvError: Summarize a String as a Number

Alteryx
Alteryx

I learned something new today that comes with a word of warning.  

 

Did you know that you can summarize a string with a SUM function?  It is not possible without this little trick.  I was working with a module that did just that.  The way that the original developer must have accomplished this magical feat was to configure the SUMMARIZE function when the field was defined as a NUMBER, then ultimately reset the value back to a string. It works.

 

Here is the other thing that I learned that could affect you.  If the incoming value is defined as a STRING and you simply cast it to a number you might receive a warning message like:

 

ConvError: Summarize (5): value: 12,345 stopped converting at a comma. It might be invalid.

 

 ConvError: Select (6): value: 12,345 stopped converting at a comma. It might be invalid.

 

Question

 

I am getting a Conversion error when trying to summarize a number with a comma in it that also happens when converting in a select:

ConvError: Summarize (5): value: 12,345 stopped converting at a comma. It might be invalid.

ConvError: Select (6): value: 12,345 stopped converting at a comma. It might be invalid.

 

Why am I getting this error?

 

Answer

 

As explained by @MarqueeCrew in the discussion boards, this conversion error occurs because the number 12345 had a comma in it. The comma is considered a string value and if you have numeric values that might contain a comma, be careful.  The sum of 10,500 and 12,750 returns as 22.

 

Your best bet is to remove the commas in your string fields that you want to be numeric before attempting to convert to ensure you get the expected results. To do this, try using REGEX_Replace like so: REGEX_Replace([String Field],"\,","")

Comments

I was comparing a column  output from two tables - supposedly numbers - but after few seconds the workflow started throwing error about "cannot use String to Number' match etc. No matter what I do about the formats nothing worked . Finally the SELECT came handy . I think Select is the ONLY way you can change format to what you want 

 

Thanks