topic Categorising data while loading the script in QlikView

Categorising data while loading the script

Wed, 09 May 2012 03:39:06 GMT

Hi All,

I have a large set of sales data for almost 150 countries from 2000 to 2011. I have further categorised them under 5 regions. Now i want to load the data in such a way that it shows only the top 5 countries based on 2011 sales figures for each region and group the rest as "Others".

I have already tried the dimension limits in the straight table with the largest 5 option but everytime it comes up with an error "Allocated memory is exceeded".

It would be great if i could make the changes in the script itself.

Help needed

Anirban

Categorising data while loading the script

Wed, 09 May 2012 05:55:26 GMT

Hi Anirban,

Are you using any calculated dimension in your chart expression? Please provide what expression and dimensions you have used in your chart.

As you have near about 10 years of data in your fact table and you are creating chart only on 2011 year data then probably you can extract latest year (2011) data in another fact and use that fact for this chart.

The other possible solution would be you use database rank function in SQL query and create another fact table in script itself which is having top 5 countries base on 2011. But if you are using QVDs then probably you need to create a seperate QVD for this functionality.

Hope this help,

Anosh

Categorising data while loading the script

Wed, 09 May 2012 06:10:36 GMT

Hi Anosh,

I have built two straight tables. One consists of the yearly data for each country from 2000-2011 and the other contains the monthly data of 2011 and 2012-till date. It also contains the month on month % change and YTD calculations.

is there any way of getting it fixed in the script only while i'm doing a reload?

Regards

Anirban

Categorising data while loading the script

Thu, 10 May 2012 08:59:42 GMT

Hi Anirban,

As per my understanding in your first table while showing yearly data for each country from 2000-2011 you display only top 5 countries that have highest sales in year 2011and you are displaying the same countries against all 11 years.

In your next table you are diplaying the top 5 countries on monthly bases for months between 2011 to 2012 till date.

If my understanding is correct you can do two things in script.

1. For first chart - In you script create seperate table having yearly data for all countries. Create seperate table to find out top 5 countries id using order by clause. Use these ids in chart for creating claculated dimension for displaying these 5 countries against all years and group others.

2. For second chart - In you script create seperate table having 2011 - 2012 monthly data for all countries. Now use this table in your chart and use dimension limit to limit no of countries based on sales.

It would be good if you create some dummy data and a qvw to explain your scenario better. Then I or someone else on this forum can probably understand your problem clearly and provide you solution.

Hope this help,

Anosh

Categorising data while loading the script

Mon, 14 May 2012 03:25:30 GMT

Hi Anirban,

Have your problem got solved?

Regards,

Anosh