Truncating Decimal.

[highpoint]

Hi,

I have stage variable decimal(14,2)
say: 666665555555.12
output needed: 5555555

Input: -666665555555.12
Output: -5555555

I would like to truncate any thing after decimal point and take right 7 digits before decimal. My destination column is Varchar 8.

I am using the following:

Code: Select all

if col<0 then "-":right(abs(col),7)
else right(abs(col),7)

please suggest me the correct way to do this.

[ray.wurlod]

Does what you're doing work? The only improvement I could suggest would be to use stage variables for clarity and to avoid multiple calculations of the same function. Maybe use Left() function to test for the sign, if the input column data type is string.

[highpoint]

Does what you're doing work? The only improvement I could suggest would be to use stage variables for clarity and to avoid multiple calculations of the same function. Maybe use Left() function to test for the sign, if the input column data type is string.

The problem with the above is it will take values to the right of decimal (precision) which i dont want.

[ray.wurlod]

That wasn't explicit in your specification.

Code: Select all

svLeftOfDecimal <--  Field(InLink.Col, ".", 1, 1)
svNegative  <--  (Left(svLeftOfDecimal, 1) = "-")
svNumber  <--  Right(svLeftOfDecimal, 7)
svResult  <--  If svNegative Then "-" : svNumber Else svNumber

[sultan@cts]

I am using the following:

Code: Select all

if col<0 then "-":right(abs(col),7)
else right(abs(col),7)

please suggest me the correct way to do this.[/quote]

you can try this code:

Code: Select all

If inlnk.inputfield < 0 Then '-':right(Field(inlnk.inputfield ,'.',1,1),8) Else right(Field(inlnk.inputfield ,'.',1,1),8)

[Sainath.Srinivasan]

Abs will return integer value. So you will loose the decimals.

Use DecimalToString to ensure the conversion. Maybe for +ve numbers you need to prefix with blank space to ensure right padded nature.

[PhilHibbs]

Abs will return integer value. So you will loose the decimals.

Technically, it returns a dfloat - but the input is an integer, so your trailing decimals get stripped off before they are passed to ABS. How's that for nitpicking? But, I see you want to strip the trailing decimals anyway, so ABS is ok.
Try this:

Code: Select all

MOD( ABS(value), 10000000 )

[highpoint]

I am using the following:

Code: Select all

if col<0 then "-":right(abs(col),7)
else right(abs(col),7)

please suggest me the correct way to do this.

you can try this code:

Code: Select all

If inlnk.inputfield < 0 Then '-':right(Field(inlnk.inputfield ,'.',1,1),8) Else right(Field(inlnk.inputfield ,'.',1,1),8)

[/quote]


My target column is varchar. Do i have to do decimal to string conversion or is not needed?

[highpoint]

Abs will return integer value. So you will loose the decimals.

Technically, it returns a dfloat - but the input is an integer, so your trailing decimals get stripped off before they are passed to ABS. How's that for nitpicking? But, I see you want to strip the trailing decimals anyway, so ABS is ok.
Try this:

Code: Select all

MOD( ABS(value), 10000000 )

Correct me if i am wrong.

The DS Developer manual has this example which shows the precision will not be lost after abs function.

This example uses the Abs function to compute the absolute value of a number.

AbsValue = Abs(12.34) ;* returns 12.34
AbsValue = Abs(-12.34) ;* returns 12.34

[arunkumarmm]

Did you try it with Field? As Sultan suggested? If not try this

Code: Select all

If inlnk.inputfield < 0 Then '-': (Field(inlnk.inputfield ,'.',1)[7]) Else Field(inlnk.inputfield ,'.',1)[7]

[kumar_s]

Correct me if i am wrong.

The DS Developer manual has this example which shows the precision will not be lost after abs function.

This example uses the Abs function to compute the absolute value of a number.

AbsValue = Abs(12.34) ;* returns 12.34
AbsValue = Abs(-12.34) ;* returns 12.34

Thats right... AsInteger() might help for this replacement. But need to check the Maximum possible operational byte by this funciton.

[PhilHibbs]

Correct me if i am wrong.

The DS Developer manual has this example which shows the precision will not be lost after abs function.

This example uses the Abs function to compute the absolute value of a number.

AbsValue = Abs(12.34) ;* returns 12.34
AbsValue = Abs(-12.34) ;* returns 12.34

That's in the Server Job Developer's Guide, but you've specified that this is in relation to a Parallel Job. Abs() in a Parallel Job converts its input to an Integer. Fabs() works with floating point values in parallel jobs.

Or, are you using a BASIC Transformer? I don't know if they behave like Server in this regard.

[highpoint]

Correct me if i am wrong.

The DS Developer manual has this example which shows the precision will not be lost after abs function.

This example uses the Abs function to compute the absolute value of a number.

AbsValue = Abs(12.34) ;* returns 12.34
AbsValue = Abs(-12.34) ;* returns 12.34

That's in the Server Job Developer's Guide, but you've specified that this is in relation to a Parallel Job. Abs() in a Parallel Job converts its input to an Integer. Fabs() works with floating point values in parallel jobs.

Or, are you using a BASIC Transformer? I don't know if they behave like Server in this regard.

Thanks, I verified abs function is removing precision for decimals.

[sultan@cts]

Code: Select all

If inlnk.inputfield < 0 Then '-':right(Field(inlnk.inputfield ,'.',1,1),8) Else right(Field(inlnk.inputfield ,'.',1,1),8)

[/quote]


My target column is varchar. Do i have to do decimal to string conversion or is not needed?[/quote]

Before using the inlnk.inputfield in derivation convert it to decimal to string.