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

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

Alteryx Help Data 2.yxdb

Datasets

Developer

Tips and Tricks

Transformation

Preparation

Accepted answers

fmvizcaino

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

2020-04-08 How to covert V_String (numeric value) to Double data type in order to get a total sum.yxmd

DavidP

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.

sum columns with null values.yxmd

All comments

fmvizcaino

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

2020-04-08 How to covert V_String (numeric value) to Double data type in order to get a total sum.yxmd

DavidP

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.

sum columns with null values.yxmd

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?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703