11 Replies Latest reply: Oct 5, 2010 5:37 PM by John Witherspoon RSS

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

      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

       

       

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

          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))


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

            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.

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

                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

                 

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

                    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?

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

                      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.