Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

Maneet_Deol
7 - Meteor

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

5 REPLIES 5
fmvizcaino
17 - Castor
17 - Castor

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
17 - Castor
17 - Castor

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.

 

DavidP_1-1586466809185.png

 

Maneet_Deol
7 - Meteor

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)
null100100
200null200
50100150
null300300
20350370

 

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

grossal
15 - Aurora
15 - Aurora

Hi @Maneet_Deol,

 

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

 

grossal_0-1586473219180.png

 

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

 

grossal_1-1586473294607.png

 

Data Cleansing Setting:

grossal_2-1586473376059.png

 

 

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

 

 

Best

Alex

Maneet_Deol
7 - Meteor

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 

Labels