Sum ( Distinct Total <Region,Year> Total_Pop) resulting out of obejct memory

[#1]

Dear Experts:

I got an out of object memory when trying to use the set analysis code below.

Each Region have multiple cities. The Population is at city level per monthly basis. So in order to get the Percentage Population

I need to divide Population for that city / Overall Population in that Region (where that city existed)

Then I had to * the result with the GRP to get the GRP for that city alone.

SUM(P_18_49_GRP*DMA_A18_49_POP)/SUM(DISTINCT TOTAL <REGION,[Brcst Year]>DMA_A18_49_POP)

If I only do 1 year - everything will be fine - The problem existed when I do more than 1 year - It will give me out of object memory issues.

Thanks

Senjaya

[#2]

I've noticed some slowdown when using the TOTAL <> modifier. How many records are in your dataset? How many unique combinations of Region and Brcst Year do you have?

If you need to display the total for many sets, it may be worthwhile to aggregate this in the load (you can use LOAD RESIDENT and a join to add a total field to your data set).

It may be a good idea to test a few years or a few different Regions until you find an amount that will work. QlikView utilizes quite a bit of RAM to work as it does. What type of system are you developing on (RAM amount, 64-bit?)?

It may be that there is too much data for QlikView to be able to handle it. You may need to put a calculation condition on the chart and force the user to make a few selections before it will display. I don't see anything that stands out, but something you could try is taking the P_18_49_GRP part out of the Sum. If it's one value for the whole city, it would be equivilent as:

P_18_49_GRP*(SUM(DMA_A18_49_POP)/
SUM(DISTINCT TOTAL <REGION,[Brcst Year]> DMA_A18_49_POP))

[#3]

i have closed to 50 millions of record per year and I store 4 years of data in the system. Total 200 Millions record

The record was stored at weekly level and There are 7 region - so 52 week x 7 x 2 for 2 years

We got 40GB and 8 Core

Before I put a restriction only to allow user pick 1 year at a time but since more user use it- and they want to do some comparison inside pivot table - then they want me to open all those restriction,

Thanks

Senjaya

[#4]

The poblem with the calculation - is they want to get the GRP at region level.

For example NY GRP 5 , NJ GRP 2 and CT GRP 2

And I need to calculate the GRP for those cstate combine :

(5* Pop in NY +2 * Pop in NJ + 2 * Pop in CT) / (Total Pop in NY, NJ,CT)

That was the calciulation they want to have.

[johnw]

OK, so you're trying to allocate the gross regional product from the region down to the cities in the region by the city population, correct?

Do you really want this to be sensitive to selections the way I think it is now? Let's say you have an "Age" field, and I select "57". Do you really then want to CHANGE the allocation to allocate the GRP based ONLY on the population of people age 57? Or do you still want to allocate based on the total population?

If on the total population, then I think this is a prime candidate for doing the allocation in the script instead of in the chart. Then I'm guessing your chart would be a simple sum of this pre-allocated value, and wouldn't have any problems.

[#6]

Actually I get the GRP from the cities level and need to roll it up to region level such as North East, West, etc.

If you select 57" - then I need the pop of 57" years old people on every cities in that region.

Pop in the city* GRp in that Cities / total Pop of all cities in that region

[johnw]

OK, so GRP is stored at the city level? Not at the state level like you show in your "for example"?

Can you just give me your basic table structure, some sample data, and the chart you display for that sample data?

[#8]

It sounds like aggregating in the script won't work, because you are making selections on the data, which would make the aggregated totals useless.

It sounds like you have a competent server, but a ton of data to go with it. I would try to reduce the data by making selections until you can get something that works, so you have an idea of when it is evaluating too much data. What if you select half of the years in the dataset, do you still get the out of memory?

Are your users really analysing the entire dataset at one time? It sounds like they are making selections to reduce the data, which should help with the memory. If they make enough selections, they should eventually be able to display the chart. I think you may have to set up the application to not render the chart until there are enough selections made to reduce the applicable data to something the app can handle.

Do you have a unique field in your dataset? If so, you can use Count(Distinct unique_field) to see how many records are being considered. You can then put a calculation condition on your chart to not render until that value is low enough. The user will then make selections and once the data is down to a reasonable amount, the chart will render.

[#9]

My bad : it should say State

Table 1 : Week, State, GRP (at State level), Spending $$

Table 2 : Year , State,, Population

Table 3 : State, Region

Table 4 : Week, Month, Year

The Chart will be pivot chart with dimension : Year, Month, Region

The Expression : Total Spend at Region level, Total GRP at Region Level.

The Total Spend is straight sum of the State in that Region

while the GRP is the issue : I have to use the calculation above to regionalized the GRP

[#10]

What is the format of the week field? Does it look like: 201052? How are you joining Week from Table 1, Year from Table 2 and Table 4? If your joins aren't right, it could cause a memory error if there is a cross join in there.