Converting column values like "14.5M" to "14500000"

[danielkcchan]

 A feature has values consisting of the amount plus a monetary unit (either "M" or 'K').  The desired conversion is to extract the amount and multiple it according to the monetary unit to get a "normalised" representation for the monetary values.  I have no problem extracting the amount and unit (as two new columns) using split with regex and the capture mode.  What I couldn't get it work is to use a computed function to produce the "normalised" amount into a new column.  The system complained that a computed function like "IF(@Value Unit@='K',  @Value Amt@ * 1000, @@Value_Amt@)" is syntactically incorrect

Can anyone help?

Daniel C

[danielkcchan · Accepted solution]

I've managed to get it work today with the following syntax:

IF(@Value Unit@ = "M", 1000000 * @Value Amt@, IF(@Value Unit@ = "K", 1000 * @Value Amt@, 0))

Not really know why it didn't work yesterday!

[JoshKF]

Daniel, 

I'm not sure offhand why one would work and the other would not. I'll take a closer look at this and see if I can figure it out. I'm glad that you were able to get it working though!