I was recently playing with a method to count the number of rows using sum-type functions and came up with this scheme (this is from my notes tab in my script).
**************************************Rick Huebner 20120607*************************
I am using numbers here so that I can work with numeric data instead of text.
This is for performance only since string processing is significantly slower than
numeric processing. The reason for using 1,000,000,000 as the Pricing transaction
type is that I can then sum instead of count the Transaction Type Column to
determine the number of each type. I assigned 1 billion to Pricing because there
will never be more Pricing lines than CostPoint lines thus preventing really huge
numbers. Also, I started with 1 billion to prevent the number of lines of cost data
from exceeding the starting Pricing point. For example: If I have 2512 lines in the
Pricing file and 2,555,444 lines in the CostPoint data a Sum([Transaction Type])
will return 2,512,002,555,444. ***NOTE*** This will only be a problem if the number
of CostPoint rows exceeds 1,000,000,000 in which case just go to 1,000,000,000,000
and change the formulas accordingly.
Broken down this is:
2,512 * 1,000,000,000 = 2,512,000,000,000
2,555,444 * 1 = 2,555,444
-----------------------------------------
2,512,002,555,444
To reverse this sum into it's constituents:
div(2512002555444, 1000000000) = 2,512
mod(2512002555444, 1000000000) = 2,555,444
CostPoint Transaction is [Transaction Type] = 1
Pricing Transaction is [Transaction Type] = 1000000000
**************************************Rick Huebner 20120607*************************
*/
I've since changed my methodology to simple flags, but noticed that when I use this formula in a text box on my 64bit system running 64bit QV11, I get the expected count of records while on my 32bit laptop I get no data, just ().
='Cost - (' & Num(mod(sum([Transaction Type]),1000000000), '#,##0', '.' , ',' ) & ')'
64bit system returns Cost - (1,050,242)
32bit system returns Cost - ()
=sum([Transaction Type])
64bit system returns 3262001050242
32bit system returns 3262001050242
I don't have a second 32bit system easily accessible to test on. I thought I might be overrunning a field length, but the sum should produce the largest number although maybe the precision on the mod function is a problem on 32bit systems?
Thoughts?
Rick
