Remove $ and brackets in numeric feild

[srinath0253]

Hi,

Im receiving input as text file.
Input: text File
TRANSACTION_AMOUNT(varchar)
($30.12)
$(30.12)
$30.12
30.12

Output: Netezza Table
TRANSACTION_AMOUNT(double precision)
-30.12
-30.12
30.12
30.12

Conditions: 1. If column contains brackets, output should be negative. Else positive.
2. $ symbol and brackets should be removed in output

Logic(which i tried):

Code: Select all

If Count(l1.TRANSACTION_AMOUNT, "(")  > 0 Then  StringToDecimal(Convert("()$", "", l1.TRANSACTION_AMOUNT)) * -1 Else   StringToDecimal(Convert("()$", "", l1.TRANSACTION_AMOUNT))

Result: im getting null values. Request your advice/suggestion.

[ray.wurlod]

Code: Select all

StringToDecimal(Convert("()$", "-", InLink.TRANSACTION_AMOUNT))

Each "(" is converted to "-", each ")" is converted to "" (removed), and each "$" is converted to "" (removed).

[chulett]

To quote an old version of a recent movie: "Clever girl!"

Not seeing what is wrong with the original code as posted, however.

[srinath0253]

Hi Ray,

Can you post the code for public. Im not premium user.

[priyadarshikunal]

When you are getting null values are you getting any warning or info message from transformer which ends with something like "Use Default value" ? I think you have spaces etc in data which is causing it to default which converting from varchar to decimal.

Try using IsValid() to find if the result you are getting is decimal and send the details using varchar field to peek to see if you are getting correct result from the derivation.

[ray.wurlod]

Hi Ray,

Can you post the code for public. Im not premium user.

Hi Srinath,

Can you become a premium member? This is the funding mechanism that keeps DSXchange alive.

[srinath0253]

Hi All,

In target, we have double precision. i changed to decimal. Now my code is working. Thanks.

Ray, i will become premium member but not now.

Im closing the ticket.