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

# Calculated dimension help

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

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

• ###### Calculated dimension help

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.