DSXchange

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

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?

My apologies, it is an Enterprise Edition job.

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

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.

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

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

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.

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)

You can do something like

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

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

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 !

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

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