Summarize tool and invalid values

Question

Hi all,

I want to use the "Sum" function in summarize, however, I keep getting "conversion errors." I believe the data type and commas within the numbers interrupt this process and causes the error. How may I fix this error? Any suggestions? Why is my "Sum" function not working for the strings after I use "grouped by"? Thank you for reading my question in advance.

Here is an example of the error:

ConvError: Select (2): (x$1000): "1,862,541" is not a valid FixedDecimal. FixedDecimal values must be of the form: -nnn.nn
ConvError: Select (2): PRN AMT: "21,264,316" is not a valid FixedDecimal. FixedDecimal values must be of the form: -nnn.nn
ConvError: Select (2): (x$1000): "1,320,892" is not a valid FixedDecimal. FixedDecimal values must be of the form: -nnn.nn
ConvError: Select (2): PRN AMT: "419,500" is not a valid FixedDecimal. FixedDecimal values must be of the form: -nnn.nn
ConvError: Select (2): (x$1000): "358,325" is not a valid FixedDecimal. FixedDecimal values must be of the form: -nnn.nn
ConvError: Select (2): PRN AMT: "113,800" is not a valid FixedDecimal. FixedDecimal values must be of the form: -nnn.nn
ConvError: Select (2): (x$1000): "195,702" is not a valid FixedDecimal. FixedDecimal values must be of the form: -nnn.nn
ConvError: Select (2): PRN AMT: "1,952,142" is not a valid FixedDecimal. FixedDecimal values must be of the form: -nnn.nn
ConvError: Select (2): (x$1000): "1,726,846" is not a valid FixedDecimal. FixedDecimal values must be of the form: -nnn.nn
ConvError: Select (2): PRN AMT: "17,225,400" is not a valid FixedDecimal. FixedDecimal values must be of the form: -nnn.nn
ConvError: Select (2): (x$1000): "84,193" is not a valid FixedDecimal. FixedDecimal values must be of the form: -nnn.nn
ConvError: Select (2): PRN AMT: "839,832" is not a valid FixedDecimal. FixedDecimal values must be of the form: -nnn.nn

SeanAdams · Answer

hey @SpiceNoodles

As @ImadZidan says, you have characters in the data that are not able to be converted.   You can clean this up using a cleanse tool, and then a select tool to change the data to the right type.

As a tip

* if you look in the results window - in the top right hand side, there's an option to switch from Data to Metadata - there you can see what the type is on the data stream.
* It's always a good idea to clean up data-types using select tools - if a field is a number, then change the type to Integer or Float and that will identify these kinds of issues with invalid / unclean data early on in your workflow.

ImadZidan · Answer

Hello @SpiceNoodles ,

It looks like your string contains unwanted characters before the conversion, in this case comma. Check the attached workflow. it gets rid of the comma before the conversion. yu may have other characters but this will give you an idea on what is happening.

conv.yxmd