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 ().
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?
