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

Converting V_WString to Number for Numeric Operator

lephunhan
5 - Atom

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:

lephunhan_0-1587039965309.png

 

  • 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.

lephunhan_1-1587040140191.png

 

Any help in this would be much appreciated. 

5 REPLIES 5
fmvizcaino
17 - Castor
17 - Castor

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
16 - Nebula
16 - Nebula

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:

 

16-04-_2020_14-52-40.png

 

Let me know if this works for you.

 

Best,

 

Roland

 

lephunhan
5 - Atom

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 😕

 

lephunhan_0-1587043351014.png

lephunhan_1-1587043362404.png

 

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

lephunhan_2-1587043522052.png

 

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

 

Thanks again! 

 

Best,

 

Nick

 

 

 

 

RolandSchubert
16 - Nebula
16 - Nebula

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
5 - Atom

Works perfectly now. Thank you so much @RolandSchubert!

 

Best,

 

Nick 

Labels