Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ben2roberts
Creator
Creator

Scripting - Group by and where sum > x

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

1 Solution

Accepted Solutions
Or
Master III
Master III

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.

View solution in original post

2 Replies
Or
Master III
Master III

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.

ben2roberts
Creator
Creator
Author

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