How to covert V_String (numeric value) to Double data type in order to get a total sum?

[Maneet_Deol]

Hello, 

I am having issues summing two different column amounts. I have two fields: Amount 1 and Amount 2 and I need to calculate the sum of both for each row. Problem is that fields are recognized as V_String values and if I try to convert the data types to Double using the Select Tool, then use the Formula Tool to sum the two columns, I get "null" for every row.

• Many rows also have "null" records. 
• There are negative values in the records ex. -12000 

I would really appreciate the help summing the two columns with how to convert them to double data types. 

Thank You, 

Maneet

[fmvizcaino · Accepted solution]

Hi @Maneet_Deol ,

Solution attached showing how to do it. When summing null values, the tonumber function helps you turn it into 0.

Let me know if you need further help.

Best,

Fernando Vizcaino

[DavidP · Accepted solution]

This is how I would do it. I assumed that the sum where Amount2 is not null and Amount1 is null should be Amount2 instead of null.

[Maneet_Deol]

First, thank you @fmvizcaino & @DavidP for your help. Your solutions are helpful, unfortunately not what I had in mind. I think I didn't do a good job explaining my issue.

Essentially how to I convert V-String numeric values to Double data type values? If you see from the database I attached Alteryx recognizes the fields are V_string types. I was thinking there may be a way to simply convert them to actual # data type and then simply Sum each row into a third "Total" column. 

Example:

Amount 1	Amount 2	Total (desired outcome)
null	100	100
200	null	200
50	100	150
null	300	300
20	350	370

I hope that makes sense or did I miss something in your solutions?

[grossal]

Hi @Maneet_Deol,

I am not quite sure what did the others did, but I'd use a three tool approach:

1) Select tool to make sure all values are doubles

2) Data Cleansing to replace "null" values with 0

3) Sum of both columns using the formula tool

Data Cleansing Setting:

Would this work @Maneet_Deol or do I also get it wrong?

Best

Alex

[Maneet_Deol]

Hey @grossal. @DavidP, and @fmvizcaino Thank you for your help. After playing around with all your proposed solutions, I ended up simply using the ToNumber formula with the Formula tool to convert the V_String numeric values to numbers for Amount 1 and Amount 2 fields. That automatically converted every null record to a 0 and then I simply added the two values for each record to get me a Total. 

Thank you, kind folks, for your help and for guiding me in the right direction. 

Best, 

Maneet