Expand my Community achievements bar.

SOLVED

Functions - Rounding by intervals

Avatar

Level 2

Hi there,

I was just wondering how you would go about rounding in intervals through functions?

For example - 

If number is between 0-10, round as 10, 10-20 as 20, 20-30 as 30.

Cheers,
Mo

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

What you could do is to use the Ceil function which rounds to the next full integer.

So if you devide by then, then use Ceil and then multiply again by 10 you should get what you are looking for.

Though if 10 should be mapped to "20" it won't work, then you may need to check for @test + 1 here

Ceil(@test / 10) * 10

View solution in original post

3 Replies

Avatar

Correct answer by
Employee Advisor

What you could do is to use the Ceil function which rounds to the next full integer.

So if you devide by then, then use Ceil and then multiply again by 10 you should get what you are looking for.

Though if 10 should be mapped to "20" it won't work, then you may need to check for @test + 1 here

Ceil(@test / 10) * 10

Avatar

Level 2

Hi Ramon,

Thanks for the response. What if the field type is text? The data is being queried from a custom ACS Table and is being sent from PEGA. It's also an eVar field and plans are to apply the function through an enrichment activity.

I tried using the the Ceil but received the error below

PGS-220000 PostgreSQL error: ERROR: operator does not exist: character varying / integer LINE 1: ...alia/Canberra' AS date), Ceil(C1.sAddressPostcode / 1000) * ... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

Avatar

Employee Advisor

if you know that it's a number, you can do a ToInteger(..) call there