Need help on rounding a decimal to nearest integer multiple

[pdntsap]

Hello,

I need help on rounding a decimal value to the nearest multiple of the given integer.

For example, if the value is 2747.334566 and the given integer is 5, the value must be rounded to 2745.
If the value is 2748.23456 and the given integer is 5, the value must be rounded to 2750.

Any help is greatly appreciated.

Thanks.

[chulett]

Can "nearest" be in either direction or is it always up? For example, let's pick 2742.334566 with 5 for the multiple, do you go up to 2745 or down to 2740 since it is nearer?

[pdntsap]

Nearest can be either direction.

2742.334566 with 5 for the multiple would go down to 2740.

Thanks.

[chulett]

What have you tried so far, anything? Any approaches that didn't quite work out that we can help refine?

I've got too many issues of my own cooking to put too much thought into this right now, only thing that comes to mind is it seems like a modulus function might come in handy here.

[ankursaxena.2003]

Hi,

You can create a stage variable sv1 of type decimal(15,0).

SV1 ---> Value(2742.334566)/Integer(5)
Destination Column ---> SV1*Integer(5)

I am not sure if this will help. But, according to me it should work.

SV1 ---> 548
Destination Column ---> 2740

[pdntsap]

Thanks Craig and Ankur.

I have implemented the following and performing some testing.

Code: Select all

if(Mod(Decimal,Integer) >= Integer/2) then
AsInteger((Decimal/Integer)+1)*Integer else  
AsInteger(Decimal/Integer)*Integer

The problem occurs when my Integer rounding value (which was assumed to be 5 earlier) turns out to be a decimal value, say. 5.2 and I would need need to round to the closet multiple of this decimal value. The modulus function throws a broken pipe error when it encounters a decimal value in the divisor part.

Thanks.

[Kryt0n]

Can you determine how many decimal places there are, multiply by 10^<num decimals> do your modulus and then divide by the same amount?

[chulett]

The problem occurs when my Integer rounding value (which was assumed to be 5 earlier) turns out to be a decimal value, say. 5.2 and I would need need to round to the closet multiple of this decimal value.

Maybe everyone else understands this perfectly but for grins since we've left the integer world behind, can you provide some examples of 'rounding to the closest multiple of this decimal value' might look like? Much like you did in your first post. Please.

[ntr]

Try this logic

Right(Field(col1,'.',1),1)=0 or 1 or 2

Then (Left((Field(col1,'.',1),Length((Field(col1,'.',1))-1):0)

Else If Right(Field(col1,'.',1),1)=3 or 4 or 5 or 6 or 7

Then (Left((Field(col1,'.',1),Length((Field(col1,'.',1))-1):5)

Else If Right(Field(col1,'.',1),1)=8

Then (Left((Field(col1,'.',1),Length((Field(col1,'.',1))-1)+2)

Else If Right(Field(col1,'.',1),1)=9

Then (Left((Field(col1,'.',1),Length((Field(col1,'.',1))-1)+1) Else NULL

[ArndW]

Let's start the ball rolling with:
StageVariable SvInteger INT(In.Col)
StageVariable SvModulo MOD(SvInteger,5)

Derivation:

IF SvModulo < 3 THEN SvInteger-SvModulo ELSE SvInteger+(5-SvModulo)

Addendum - oddly, I didn't see any of the other posts when I posted this and didn't realize that there had been some discussion already. But too late to remove my post.

[chulett]

No, as long as there is no reply posted you could have deleted your post should you have so desired. Now however... it's too late.

[chulett]

The problem occurs when my Integer rounding value (which was assumed to be 5 earlier) turns out to be a decimal value, say. 5.2 and I would need need to round to the closet multiple of this decimal value.

Maybe everyone else understands this perfectly but for grins since we've left the integer world behind, can you provide some examples of 'rounding to the closest multiple of this decimal value' might look like? Much like you did in your first post. Please.

OK, maybe this. Remove the decimal piece so you are back to an integer, do your thing and then add the decimal back on.

[ntr]

<removed your complete reposting of my last response and your idea>

WON'T IT WORK???

[ankursaxena.2003]

Let's say your number is 174.12 and you are trying to divide by 5.2.

What would you round this to.
1) 170
2) 171.6
3) 176.8
4) Any other number.

[chulett]

<removed your complete reposting of my last response and your idea>

WON'T IT WORK???

No need to post all of that again.

Heck if I know, it's nothing I can test right now and (to me) it looks overly complicated and not generic enough. [shrug] We'll see what pdntsap says.