Convert value from -00005.55 to -5.55

[Marley777]

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

Thanks

[anbu]

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

[Marley777]

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?

[anbu]

Define your target as varchar

[Marley777]

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

[anbu]

Code: Select all

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

[Marley777]

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?

[HariK]

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

[Marley777]

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.

[ray.wurlod]

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

[arunkumarmm]

Can you try something like this:

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

[Marley777]

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

[priyadarshikunal]

have you tried decimaltostring with suppress_zero as rtype?

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

[akbar2002]

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

[ray.wurlod]

Did you mean DecimalToString() ?