Help with limiting records in dataset of 1 million+ records

[#1]

Hello QV Community,

I have a dataset that has 1 million records containing account numbers. What I'm attempting to do is allow for all the records to be viewable in a single table, but I get an "Out of Object Memory" error. Since it looks like I cannot display 1 million + records, I was hoping to be able to allow for the first 65,000 to be viewable in a table and subsequently be able to export this table to excel. I've made a few attempts with aggr() and rank() but haven't seem to be able to get something to work perfectly.

Does anyone have any idea on how I can get something like this implemented? Below I have example code that I have tried and it did not work. As a side note, the Account Number field is being passed into the equation with the variable vSelectDimension1, a variable that the user can change using a listbox

(Aggr(Rank($(='['& vSelectDimension1 & ']')),$(='['& vSelectDimension1 & ']'))<64000,$(='['& vSelectDimension1 & ']))

Thanks in advance for all your great insight!

Alex

[jagan]

Hi Alex,

Try like this in attached file.

Regards,

Jagan.

[#3]

Jagan,

Thanks for your post. This won't quite help me accomplish what it is I'm trying to do. Each account number is unique and cannot be grouped/filtered in the same way you have.

Alex

[#4]

Alex,

Did you tried Jagan's recommendation. I hope it will work you. He didn't group IP address.. His grouping based on rowno(). So same approach should work for you. Try once again and revert if you face any issue..

Karthik

[#5]

This error normally occurs when you have a low RAM pc. If you want to run your app and records smoothly you might need to upgrade the RAM. Upgrading the RAM of the system will not only be usefull for this particular dashboard but also be helpfull for you to handle large data sets in future. You can use different methods to display limited data for now but it will be a temporary solution. What if in future you have to show all the million records in table? Upgrading your system will be the only good option left for you.

[#6]

If you want to display you accounts limited in number you can use set analysis on the basis of account creation date. Limit the number of results using set analysis. e.g. count({<Year={'2012'}>}AccountNo) this will return you the accounts created in 2012 only.

[flipside]

Hi Alex,

Try a conditional show on your object where count of rows is <= 65000. Or better than that use a calculation condition for the same. You can then configure a bespoke error message where the calculation condition is unfulfilled to show a message in the object saying something like "Values will show when number of rows is 65000 or less".

flipside

EXAMPLE:

Data:

Load rowno() as Value, ceil(rand()*5) as Group autogenerate 10000;

Create a listbox for Group and a Table Box with Value and Group.

Set the Calculation Condition to ...  count(distinct Group)=1

Set the Error Messages ... Calculation conditionunfulfilled to ... Pick only one group

[#8]

Hi flipside,

Thanks for your response. A conditional show is no good. What I would like to do is give the client a view of 65,000 or the 1mil+ rows without any filtering applied. This way, they can do some quick ad-hoc analysis or identify extreme accounts. What I mean by this is when the client selects [Account Number] as the dimension, they can also select a Metric (Gross Charges, Days Open etc.) and the dimension is sorted Descending upon that Y-value.

Being that it is not a static box, I'm using a pivot table not a table box.

Any other ideas out there?

Alex

[#9]

Hi Hannan,

I totally agree, unfortunately I can't guarantee that my clients will always have that option to just increase their RAM when they can't view a table nor would I expect them to do so. Our server also crashes when the object attempts to display 1mil+ records in a table. Any way to just show the top 65,000 rows would be the best solution.

Thanks for your response!

Alex

[#10]

Hi Karthik,

Thanks for your response, I now understand Jagan approach. Unfortunately, the pivot table dimensions and expressions are set with variables that the client can pick and change so setting up groups based on rowno() in the load script will not work. The client is able to select from a list box the dimesions and metrics and when they select [Account Number], for which there are over 1 million, the pivot table displays the error. What I really need is a way to just show the first 65,000 records. Any thoughts?

Alex