Decimal field with Trailing negative signs
Moderators: chulett, rschirm, roy
Decimal field with Trailing negative signs
Hi,
Just wondering if anyone knew of a good way to deal with decimal fields that, if negative, have trailing negative signs on import.
Ex:
1,234.05-
22,000.00
When converting from char to decimal it does not like the negative sign.
Any suggestions?
Thanks,
Adam
Just wondering if anyone knew of a good way to deal with decimal fields that, if negative, have trailing negative signs on import.
Ex:
1,234.05-
22,000.00
When converting from char to decimal it does not like the negative sign.
Any suggestions?
Thanks,
Adam
Adam Love
I just attempted it.
I am recieving an error:
APT_CombinedOperatorController,0: Numeric string expected for input column 'huon_prem'. Use default value.
I am importing in as a varchar, data looks like:
".00"|"5084.00-"
"203015.00"|"189710.00" (delimited by | )
I have a transform stage adding a new column and saving the above 2 fields as decimals. This is where I am occuring issues, I am assuming a function is needed to transform to decimal however I am unaware of what to use.
I am recieving an error:
APT_CombinedOperatorController,0: Numeric string expected for input column 'huon_prem'. Use default value.
I am importing in as a varchar, data looks like:
".00"|"5084.00-"
"203015.00"|"189710.00" (delimited by | )
I have a transform stage adding a new column and saving the above 2 fields as decimals. This is where I am occuring issues, I am assuming a function is needed to transform to decimal however I am unaware of what to use.
Adam Love
You can do something like
Code: Select all
If Right(in.Col, 1) = "-" then "-":left(in.Col, len(in.Col)-1) else in.Col
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
The string manipulation worked well, however is there a better command than len(in.col) as it returns the max length of the string, not its current trimmed length (just wondering).
Also, I agree that with a proper datatype setup this would be handled much more effectively. However, does it matter that I only will ever have a "-" on the end of my decimal and never a "+" ?
And by the way, this is one of my first times using these message boards and I am very suprised at the quick response time and knowledgeable replys. Thanks for your help !
Also, I agree that with a proper datatype setup this would be handled much more effectively. However, does it matter that I only will ever have a "-" on the end of my decimal and never a "+" ?
And by the way, this is one of my first times using these message boards and I am very suprised at the quick response time and knowledgeable replys. Thanks for your help !
Adam Love
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio