Extracting entries with round numbers (Idea ver 10.3)

Denise614

Forums

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

Steven Luciani Mon, 04/30/2018 - 15:08

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 

 

 

Denise614 Mon, 04/30/2018 - 16:23

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
 

Avalanche2018 Tue, 01/14/2020 - 05:07

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. 

Brian Element Tue, 01/14/2020 - 11:17

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.