Convert text to dollar numeric value

rbross

Hello,

I have an text attribute that needs to be convert to a numeric attribute.
using BO1.DollarValue=AS_NUMBER(BO1.TextDollarValue) does not do anything...

I have checked the numeric formatting on the Numeric attribute and set it to be currency, format is $###,###.00 but cannot get it to convert when I run the process. the other numeric attribute which is a whole number converted fine. Is there something different you have to do when dealing with the decimal point?

customaware

Not in front of a computer but maybe try....

BO1.DollarValue=AS_NUMBER(BO1.TextDollarValue,'$###,###.00')

or even

BO1.DollarValue=AS_NUMBER(BO1.TextDollarValue,'n')

rbross

Tried the first one and it did not work. I will try the second option. thanks Mark.

rbross

Plant.Price_N=AS_NUMBER(Plant.PRICE,'n') this does not work either.
There has to be a way to get an alpha numeric field into a numeric field format...

customaware

Try it without the $ sign

rbross

Nope same result.

customaware

What does the text data look like Roger?

4xjbh

I tried to do this last week and I kept getting something like '2e+1'. I watch with anticipation....

customaware

You need to strip the $ sign from the front of the TEXT attribute.

If CurrencyConvertTest.TextNumber WAS CHANGED Then
CurrencyConvertTest.NumberNumber=REPLACE_PATTERN(CurrencyConvertTest.TextNumber,'$','')

Will NOT work. However, this does....

If CurrencyConvertTest.TextNumber WAS CHANGED Then
CurrencyConvertTest.NumberNumber=REPLACE_PATTERN(CurrencyConvertTest.TextNumber,'\$','')

Jaser

Hi,

I am not using the currency format but looking at the User guide appendix C, it indicates that you got to suppress the following characters -when exist in your string-: [ "$", ",", "(", ")" ]

Hopefully you are not using negative numbers so we will cancel manipulating "(" and ")".
then use some text functions to find the number, following a partial case with LENGTH and SUBSTRING functions:

If LENGTH (BO1.TextDollarVallue)<= 7 THEN
BO1.DollarValue=As_Number(SUBSTRING (BO1.TextDollarValue, 1, 6)

Hope this will work.

Regards,
Jaser

rbross

Problem Solved!
Marks example works great.

BO1.Price_N=REPLACE_PATTERN(BO1.PriceText,'\$','')

Jaymer

so if the text version was strictly numbers and a decimal point, then all you have to do is this?

Price_N=PriceText

and AIM auto converts the type?

rbross

Jaymer wrote
so if the text version was strictly numbers and a decimal point, then all you have to do is this?

Price_N=PriceText

and AIM auto converts the type?

You still need AS_NUMBER() Function
Price_N=AS_NUMBER(PriceText)

My example needed the $ removed that's why it was not working.