Functions - Rounding by intervals

[user03817]

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

[ramon_bisswanger · Accepted solution]

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

[user03817]

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.

[ramon_bisswanger]

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