Page 1 of 1
Truncating Decimal.
Posted: Tue Aug 24, 2010 6:10 pm
by 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.
Posted: Tue Aug 24, 2010 6:21 pm
by 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.
Posted: Tue Aug 24, 2010 8:28 pm
by highpoint
ray.wurlod wrote: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.
Posted: Tue Aug 24, 2010 9:15 pm
by 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
Re: Truncating Decimal.
Posted: Wed Aug 25, 2010 1:52 am
by 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)
Posted: Wed Aug 25, 2010 2:03 am
by 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.
Posted: Wed Aug 25, 2010 2:21 am
by PhilHibbs
Sainath.Srinivasan wrote: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:
Re: Truncating Decimal.
Posted: Wed Aug 25, 2010 8:41 am
by highpoint
sultan@cts wrote: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?
Posted: Wed Aug 25, 2010 8:49 am
by highpoint
PhilHibbs wrote:Sainath.Srinivasan wrote: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:
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
Posted: Wed Aug 25, 2010 9:04 am
by 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]
Posted: Wed Aug 25, 2010 9:06 am
by kumar_s
highpoint wrote:
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.
Posted: Wed Aug 25, 2010 9:25 am
by PhilHibbs
highpoint wrote: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.
Posted: Wed Aug 25, 2010 6:19 pm
by highpoint
PhilHibbs wrote:highpoint wrote: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.
Re: Truncating Decimal.
Posted: Thu Aug 26, 2010 12:30 am
by 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.