3 Replies Latest reply: Sep 14, 2011 5:45 PM by ianmcgivern RSS

    Calculated dimension help

    Don Saluga

      I'm trying to create a Pivot chart that uses a subset of  hundreds of product codes for analysis so I thought I would put the specific product codes in a calculated dimension to act as a filter.  I used the following code for the calculated dimension but it is giving me an error message like Allocated Memory Exceeded. My code is as follows:

       

       

      =If (PRODUCT_CDE= 'BF','BF5','SF','SF5')

       

      I will be adding even more codes so any advice would be appreciated.

       

      Don

        • Calculated dimension help

          Hi dsaluga - seeing that you know the products before hand - why don't you create an inline table in your load script and then use if(ANALYZE = 'Yes', PRODUCT_CDE) in your dimension. your inline table could look like this (It will also be alot less memory intensive than creating deep levels of nested if's in your front-end):

          LOAD

           

           

          * INLINE [
          PRODUCT_CDE, ANALYZE
          BF, Yes
          BF5, Yes
          SF, Yes
          SF5, Yes
          ABC, No
          DEF, No
          ]
          ;

           

            • Calculated dimension help
              Don Saluga

              Ian, thank you for your help.  That will probably work.  I get these product codes from an SQL database and there may be 50 or more to analyze.  I also have used an SQL case statement in the load script that flags the code I need. The database is very fast and does all the work so QlikView just reads the flag.

               

              For example

              Case

              When product_cde in (BF, SF etc)

              Then 'I'

              When product_cde in (AB, CD etc)]

              Then 'E'

              Else X End as Product_Flag

               

              Using set analysis in the chart I can simply put Product_Flag = {'I'} to filter these products. There are probably different ways of doing this. 

                • Calculated dimension help

                  dsaluga, another way (if you want QlikView to handle) is to use mappings. Do a mapping load with your products and their "Categories". then applymap(PRODUCT_CDE, whatever). I am not sure whether it was their intended purpose, but whenever I come across a challenge where I would have used a Case statement in SQL - I use mappings.