Negative Number representation needs to be modified

[csc.datastage]

Hi All,

Values for a source column in one of my job are as follows:

123.45-
985.24
234.67-
875.89


123.45- represents a negative number ( -123.45)

I need to look for - at the end and if found convert this number to -123.45 so that I can perform mathematical calculations with this value.

So my output should be:

-123.45
985.24
-234.67
875.89

Can anyone please suggest how this can be achieved?

Thanks a lot for all your help and guidence.

[chulett]

Treat the sign as a separate field and when it is "-" multiply the amount by -1.

[csc.datastage]

Treat the sign as a separate field and when it is "-" multiply the amount by -1.

Thanks Craig!
I am stuggling with how to code when part. Is there any function like:

if found "-" then ....else ...

or something on those lines?

Take care!

[DSguru2B]

You can try the INDEX() function to detect - sign and if present, take the entire field except last byte and multiply it by -1.

[chulett]

Can you define that part of the data as a separate field? If so then just check that field directly. If you can't, you check the last character of the field and then multiply the rest. Assuming the latter:

Code: Select all

If Link.AmountField[1] = "-" Then
  Link.AmountField[1,Len(Link.AmountField)-1) * -1
Else
  Link.AmountField

The "[]" are the substring operators and a single position by itself denotes from the end of the string. So, it checks to see if the last character is "-" and if so, take everything else (from 1 to the end -1) and multiplies it. You may need to trim your field before the check to make it work correctly. Or you can use the Index() function to detect the minus sign and take everything before it for the math part:

Code: Select all

If Index(Link.AmountField,"-") Then
  Link.AmountField[1,Index(Link.AmountField,"-")-1) * -1
Else
  Link.AmountField

Index returns the position of the found character and a non-zero value equates to "true" so it also works as a boolean. You should do the check in a stage variable and reference that in the expression so you don't have to compute it twice.

[DSguru2B]

I would do something like

Code: Select all

If Index(Link.AmountField,"-") Then 
  CONVERT('-','',Link.AmountField) * -1 
Else 
  Link.AmountField

[chulett]

Sure, all kinds of ways to skin this cat. And I edited my post to remove the "End" statements from the code as they are not used in a derivation.

[csc.datastage]

Thanks a lot Craig and DSGuru!

Both methods worked!
I will also use Stage Variables as it has to be done across multiple columns.

Take care!

[JRodriguez]

Hi, Try this


Oconv("123456-", "MC/N") : Oconv("123456-", "MCN")

[ray.wurlod]

Way too complex! Iconv(inLink.TheValue, "MD2-") will handle the trailing sign.

[chulett]

Ah... the Masked Decimal conversions. They're not really all that well explained in the docs, you mostly have to infer how they work from the examples they include. What exactly does "MD2-" mean? A scale of two with a trailing minus sign?

[ray.wurlod]

They are explained well in the UniVerse docs. "MD2-" indicates two decimal places with a trailing sign. Note, though, that the result of Iconv() has masked decimal - to reinstate you would need an Oconv().

Code: Select all

Oconv(Iconv(InLink.TheValue, "MD2-"), "MD2")