DSXchange

Hi, would like to convert -00005.55 to -5.55. Field is a decimal field length of 5 and a scale of 2.

Thanks

Leading zeros does not affect the decimal value. You can assign your input directly to your target of Decimal(5,2)

The end users do not want to see the leading zeroes. Out target is a file. so wondering how we can go from -00005.55 to -5.55?

Define your target as varchar

We tried varchar but it did nothing came back as -00005.55

We also tried Trim(Convert(' ','',(Format.PAID)),"0","L") worked for positive numbers, but not negatives, negatives were unchanged.

Any other ideas

Thanks

That gives us ,-5.000000 Puts zeroes on the other side. works for positive, but not negative. Not sure how to get rid of the zeroes to the right now?

convert the negative into positive(multiply by -1), do the same conversion as you did for positive and then convert back to negative.

Still gives us the zeroes on the right of the decimal position.

Tried multiplying -00005.00 * -1 to get 00005.00, then applying the conversion, and then concatenating a negative sign on the front. It still came out -5.000000.

We still have the zeroes on the end.

Show us the precise expression that you used. This will enable our assistance to be more exactly targeted.

Can you try something like this:

If Input.Column < 0 Then '-' : Trim(Field(Input.Column,'-',2),'0','L') Else Trim(Input.Column,'0','L')

If we split the logic accross 3 stage variables it works. Our output is a varchar field. But not sure why we needed to do it this way; please read below.

example (the logic below works when splitting the formula)

svPaidIn
If Format.PAID < 0 Then Format.PAID * -1 Else Format.PAID

svPaid
Trim(Convert(' ','',(svPaidIn)),"0","L")

svPaidTarget
If Format.PAID < 0 Then '-':svPaid Else If svPaid[1,1] = "." Then "0":svPaid Else svPaid

but when we do the logic below it puts 0's to the right. Same formula as above, but not split accross multiple lines/stage variables like we saw in the previous example. Is it doing the Trim(Convert first them multiply by -1? Not sure why it doesn't do the correct order of operations when all together in one formula, but when we split it out it works?

svPaid
If Format.PAID < 0 Then Trim(Convert(' ','',(Format.PAID *-1)),"0","L") Else Trim(Convert(' ','',(Format.PAID)),"0","L")

svPaidTarget
If Format.PAID < 0 Then '-':svPaid Else If svPaid[1,1] = "." Then "0":svPaid Else svPaid

have you tried decimaltostring with suppress_zero as rtype?

Can't check it but you can give it a shot.

SV1 : StringToDecimal(column_name) --> DataType = VarChar
SV2 : Field(SV1,'.',1) --> DataType = Integer
SV3 : Right(SV1,2) --> DataType = VarChar

Derivation : SV2:'.':SV3 --> DataType = VarChar

Let me know if you see find any issues

Zak

Did you mean DecimalToString() ?