Converting V_WString to Number for Numeric Operator

[lephunhan]

Hi all,

I have the following dataset that is currently in V_WString type but I would like to perform a mathematical operation on it and I am struggling to find a way to do it. What I would like to achieve:

• Write an IF THEN statement to say that if [account] > 1999 and [account] < 4000 then [balance] * -1 ELSE [balance] ENDIF

Whenever I try to write that, I keep getting an error that says I cannot perform numeric operator to string value. To my understanding, that just means since [account] and [balance] are V_WString, I cannot perform numeric operator on this. But I am struggling to find a good solution to essentially perform the numeric operator for the above.

Any help in this would be much appreciated. 

[fmvizcaino]

Hi @lephunhan ,

You have two options there. One is to use a select tool right before and convert the Account and Balance column to integer and double.

Another you is to use the formula function ToNumber([Account]), that way you will only transform for this specific calculation and keep everything in the string format

Best, 

Fernando V.

[RolandSchubert]

Hi @lephunhan ,

if you modify your formula a bit, it should work (calculated field should be double):

IF [account] > 1999 AND [account] < 4000 THEN

  ToNumber([balance]) * -1

ELSE

   ToNumber([balance])

ENDIF

If you want to execute the calculation on the the field [balance], you have to change thefield type (using a Select tool) or use a Multi-Field Formula tool, you can calculate and  change the field type in one step:

Let me know if this works for you.

Best,

Roland

[lephunhan]

Hi @RolandSchubert

Thank you for the detailed solution! 

For some reason, I keep getting an error saying that I've got an invalid type of operator. I basically dropped in a new formula tool and created a new column with the formula stated but it's not working for some reason 😕

This is the select tool indicating that both [account] and [balance] are V_WString type:

Could you help me understand where I could have gone wrong in this?

Thanks again! 

Best,

Nick

[RolandSchubert · Accepted solution]

Sorry, I missed that point - [Account] is a string as well ... so the ToNumber conversion is necessary for this field as well.

If you replace the formula with:

IF ToNumber([Account]) > 1999 AND ToNumber([Account]) < 4000 THEN
ToNumber([_CurrentField_]) * -1
ELSE
ToNumber([_CurrentField_])
ENDIF

it should work (hopefully).

Best,

Roland

[lephunhan]

Works perfectly now. Thank you so much @RolandSchubert!

Best,

Nick