topic Calculated dimension help in QlikView

Calculated dimension help

Tue, 13 Sep 2011 20:29:30 GMT

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

Tue, 13 Sep 2011 21:48:16 GMT

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

Wed, 14 Sep 2011 12:45:30 GMT

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

Wed, 14 Sep 2011 21:45:13 GMT

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.