Scripting - Group by and where sum > x

[Anonymous]

Dear all,

I am trying to move a calculation into my load script for performance reasons but am struggling a little bit with the best way to do so.

My problem is I have a master file with >1m rows of data and I want to count the number of occurrences of a product but only where a sum condition is met. I was able to do this with a two step process of:

1. Create a temporary table using a resident load of the master which sums the product with amount (i.e. consolidates multiple rows into 1 for each product in each year)

2. Create another resident load on this temp table to run a count and use where clause >1000

This gives me a new table with the count of products where the amount >1000 for each client, each year.

As the table in question is vast I am concerned about the performance of multiple loads of the data and was seeking a more elegant solution to perhaps combine the above into one step.

I know that this could also be accomplished with set analysis in my main dashboard but this was causing very slow performance I believe due to the complexity and size of the actual data model and not this sample.

I have included sample QVW and data file.

Thanks,

Ben

[Or · Accepted solution]

Using a double-load is indeed the correct way to handle this. A million rows should not be a problematic number - this should have no issues running, unless you've got unusually large fields. I do similar things with >10m rows in a QVW that runs once an hour.

Given that the information is already in-memory, you typically don't need to worry about adding resident loads unless you're in an every-second-counts scenario.

[Anonymous]

Thanks for your comments OR. Glad to know I am on the right track.