Decimal field with Trailing negative signs

a2love · Post by **a2love** » Mon Feb 12, 2007 10:16 am

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

ArndW · Post by **ArndW** » Mon Feb 12, 2007 10:35 am

Adam, how this question is answered depends upon whether is a server job (as per your "Job Type") or a PX one (as per your select forum). Which is it?

a2love · Post by **a2love** » Mon Feb 12, 2007 10:41 am

My apologies, it is an Enterprise Edition job.

ArndW · Post by **ArndW** » Mon Feb 12, 2007 10:49 am

Have you tried declaring it as a decimal field, then giving it a "unpacked, separate" and "trailing sign" in the attributes?

a2love · Post by **a2love** » Mon Feb 12, 2007 1:41 pm

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.

narasimha · Post by **narasimha** » Mon Feb 12, 2007 1:44 pm

You need to transform "5084.00-" into "-5084.00" before converting it into a valid decimal value.

DSguru2B · Post by **DSguru2B** » Mon Feb 12, 2007 1:45 pm

Is (-) the only trailing character you are getting or there are others? Is this a packed field? What does the trailing (-) represent?

ArndW · Post by **ArndW** » Mon Feb 12, 2007 1:45 pm

Since the source data is enclosed in quotes you will need to set the quote to "double" on "edit row" so that it can be parsed and removed on input and specify the datatype as decimal.

a2love · Post by **a2love** » Mon Feb 12, 2007 1:47 pm

thats what i thought... any tips on accomplishing that ?

I can use Convert() to remove the end "-". But is there some type of boolean operator I can use to identify if this string is present? (so I can use this logic: If present then remove from end and put on front, else do nothing)

DSguru2B · Post by **DSguru2B** » Mon Feb 12, 2007 1:49 pm

You can do something like

Code: Select all

If Right(in.Col, 1) = "-" then "-":left(in.Col, len(in.Col)-1) else in.Col

ArndW · Post by **ArndW** » Mon Feb 12, 2007 1:51 pm

There is no need to do any string manipulation or conversion if the datatype is specified correctly in the first place.

DSguru2B · Post by **DSguru2B** » Mon Feb 12, 2007 1:55 pm

ArndW wrote:There is no need to do any string manipulation or conversion if the datatype is specified correctly in the first place.

Point duely noted

a2love · Post by **a2love** » Mon Feb 12, 2007 2:12 pm

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 !

DSguru2B · Post by **DSguru2B** » Mon Feb 12, 2007 2:18 pm

Go for len(trim(in.Col)) - 1. But ArndW has a very valid point. No string manipulation is required.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Feb 12, 2007 2:19 pm

a2love wrote: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).

len(Trim(in.col," ","A"))

I am trying to be funny:-P