Looking for way to extract round numbers from numeric field in database. I'd prefer to keep field as numeric. Thanks in advance.

Denise614

Offline

Last seen: 1 year 9 months ago

Joined: 04/30/2018 - 12:46

Hi Denise,

If your are attempting to do some even dollar testing of say a bank statement file, then you would use the modulus feature of IDEA found in the equation editor. If you look on the equation bar, third button from the right you will see the letters MOD.

To use this feature to look for even dollar amounts divisible by 100 your equation would be:

AMT(numeric field with the dollar amount in it) % (symbol that shows up when you click the MOD button 100 (divisor amount) = 0 (remainder)

AMT % 100 = 0

To extract these round amount you left click on the Criteria link in the Properties window, which brings up the equation editor, then you write the equation above and execute it. Only round numbers will be displayed. Then you either go to home save as, or the direct extraction feature to extract the even dollar amounts.

If you are looking for something different please expand upon your initial query and I'll do my best to answer your question.

Cheers,

Steve

Steve- That's fantastic. I'm testing journal entries and I knew there had to be something straightforward for this but I just couldn't locate it in Help section. What I ended up doing before I got your response was an extraction using equation Amount - (@int(Amount) =0 and Amount > 0 in order to get debits(credits) with round positive values. Less is better. Thanks for yours.

And cheers back!

Denise

Hi Steven,

Im also doing testing for amounts ending in 999. Do you happen to know how to extract amounts with those figures? I would greatly appreciate it. Thanks.

There are a couple of ways to do this depending if the 999 contains the decimals or not. If they don't contain the decimals, so a number such as 12,999.25 would be selected then you could use something like this:

@Right(@str(@int(NUM_FIELD), 12, 0), 3) = "999"

Here I first remove the decimals by using the integer function, I then change the number to a character and test if the 3 right most characters are 999.

If you want to keep the decimal so a number like 12,349.99 would be selected then you could use:

@Right(@Remove(@str(NUM_FIELD, 12, 2), "."), 3) = "999"

Here I change the number to a character, remove the decimal and then test to see if the 3 right most characters are 999.