topic Help required in creating aggregation table! in QlikView

Help required in creating aggregation table!

Wed, 20 Apr 2011 06:14:37 GMT

Hi All

The performance of one of my pivot table is very bad as it does lots of calcuation in the front end. So thought of creating the aggregation table so as to improve the performance of it. As I never created such aggregation table before can some one help me out on this please. I have given you the details about the tables as well as the pivot table. Also have attached the sample application as well.

Following are the tables(Data & Calendar) which are there in the attached application:

Data:

LOAD A1,
A2,
A3,
A4,
A5,
A6,
A7,
A8,
A9,
A10,
A11,
A12,
A13,
A14,
TimeStamp(A15) as StartDate,
TimeStamp(A16) as EndDate,
A17 as StartDate1,
A18 as EndDate1,
A19,
A20
FROM

(ooxml, embedded labels, table is Sheet1);

Calendar:

Load *,
Week(date) as Week,
Year(date) as Year,
WeekName(date) as WeekName,
MonthName(date) as MonthName
;

LOAD DISTINCT
DATE(StartDate+IterNo()-1) AS date
RESIDENT Data
WHILE StartDate+IterNo()-1<=EndDate;

Where as the pivot table contains:

Dimension:

A10

StartDate

EndDate

Time(Group(Cyclic): Week,Year,WeekName,MonthName)

Expression:

SUM(IF(StartDate<=date AND EndDate>=date,1))

Help required in creating aggregation table!

erichshiino — Sat, 23 Apr 2011 03:43:45 GMT

Hi,

I didn't know which level you could aggregate, but in order to remove the if condition in your pivot table I created a table with one line for each day between start date and end date for each item of your table.

Basically, you get performance but you create more lines in you data model.

This is the code i got:



Data:
LOAD A1,
 A10,
 A1 & '|' & A10 as key,
 A11,
 A12,
 A13,
 A14,
 A19,
 A2,
 A20,
 A3,
 A4,
 A5,
 A6,
 A7,
 A8,
 A9,
 EndDate,
 EndDate1,
 StartDate,
 StartDate1
FROM

(biff, embedded labels, table is Sheet1$);

Keys:
load distinct key, 1 as count_key
resident Data;


min:
LOAD MIN(num(daystart(StartDate))) as minDate
resident Data;

vMin = peek('minDate');
max:
LOAD max(num(daystart(EndDate)+1)) as maxDate
resident Data;
vMax = peek('maxDate');

drop tables min, max;


for refDate = $(vMin) to $(vMax)

Event:
load key, $(refDate) as REFERENCE_DATE, 1 as controlFlag

resident Data
where num(dayStart(StartDate)) <= $(refDate) and
 num(dayStart(EndDate)+1) > $(refDate) ;



next

TRef_Calendar:
load distinct
REFERENCE_DATE
resident Event;

Ref_Calendar:
load
REFERENCE_DATE,
 Week(REFERENCE_DATE) as refWeek,
 Year(REFERENCE_DATE) as refYear,
 WeekName(REFERENCE_DATE) as refWeekName,
 MonthName(REFERENCE_DATE) as refMonthName
resident Event;

drop table TRef_Calendar;
Calendar:


Load *,
 Week(date) as Week,
 Year(date) as Year,
 WeekName(date) as WeekName,
 MonthName(date) as MonthName
 ;

LOAD DISTINCT
 DATE(StartDate+IterNo()-1) AS date
RESIDENT Data
WHILE StartDate+IterNo()-1<=EndDate;


exit script ;

Help required in creating aggregation table!

Mon, 25 Apr 2011 11:43:24 GMT

Hi Erich Shiino

Thanks alot! Really a great effort!

By the way may I know what is that I have to do instead of 'IF Statement' in the expression. So that I can pass the same and see how it is performing.

Help required in creating aggregation table!

erichshiino — Mon, 25 Apr 2011 12:45:25 GMT

Hi,

With the new script, if you use the fields in Ref_Calendar, you already have this if condition that you had before in the pivot table.

If you want to count distinct values, you can just sum the controlFlag (there is one for each 'key' in the model (key is A1+A10)

Ref_Calendar:
load
REFERENCE_DATE,
 Week(REFERENCE_DATE) as refWeek,
 Year(REFERENCE_DATE) as refYear,
 WeekName(REFERENCE_DATE) as refWeekName,
 MonthName(REFERENCE_DATE) as refMonthName
resident Event;


Rgds,

Help required in creating aggregation table!

Mon, 25 Apr 2011 13:05:48 GMT

Sorry! I didn't get you! I have used 'Sum(controlFlag)' in the expression but it is not working. If you have free time please try to do the required changes in the attached application it will be helpful for me. As I don't what changes has to be done in the front end I could proceed further.

Help required in creating aggregation table!

Tue, 26 Apr 2011 11:25:59 GMT

Hi Erich Shiino

Was I am not clear? If so please do let me know. If your clear about what I have asked then please try to help me out.

Help required in creating aggregation table!

Thu, 28 Apr 2011 12:48:08 GMT

Can some one please help me out here. I didn't get any solution yet. It is very important for me to complete this task.

Hope you guys can understand that!

Help required in creating aggregation table!

erichshiino — Thu, 28 Apr 2011 20:56:03 GMT

Hi, Please, check attachment. I left you table on the first sheet and created another one on the the other. I'm not sure how to get your results, for example, if select the dimension Year, i can get more than 365 in one line for one year. In my pivot I can not reproduce it. If you explain I can adjust the app.

Sorry I couldn't send it before...

Regards,

Help required in creating aggregation table!

Fri, 29 Apr 2011 07:18:45 GMT

Hi Erich Shiino

Yes I am not sure how to get the results. As I said some how I got the solution in the forum and luckily it is working. But only problem is there is lot of performance issue. I don't know what to explain as I got the expected result but the performance is is poor.

Look into it if you get any idea or else its ok. Anyway I am still working on the performance.

Help required in creating aggregation table!

erichshiino — Fri, 29 Apr 2011 11:49:50 GMT

but is that result right? I mean, if I see only line and select year as dimension can it get to 800, for example?

I can't reproduce it because I don't know what that mean.

Help required in creating aggregation table!

Fri, 29 Apr 2011 12:33:26 GMT

Hi Erich Shiino

Yes I am getting the right result. Only concern is the performance.

I didn't understand what is that you have meant above. Could you please explain me in detail?

Help required in creating aggregation table!

erichshiino — Fri, 29 Apr 2011 12:37:26 GMT

The example I created does not work for you, does it?

It can increase performance, we just need to adjust the expression but I don't know what your expression really is to make it work on my example.

Help required in creating aggregation table!

Fri, 29 Apr 2011 13:14:21 GMT

Yes it works and performance has been improved as well. Only concern is the expression. I also don't know how to make it work. It looks like a simple 'IF Statment' only but don't know how it works. I got fed up to understand how it works 😞

Help required in creating aggregation table!

erichshiino — Fri, 29 Apr 2011 13:58:54 GMT

I'd like to understand this result to be able to reproduced it in the improved pivot table:

A1	A10	StartDate	EndDate	Time	2010/52
21268	Qpksnr g vfmdlz Cfxúe Vqsbdasw	15/03/2010 11:51:00	01/04/2011 09:55:00		36

Why this result within one week is 36? Shouldn't it be 7?

Help required in creating aggregation table!

Mon, 02 May 2011 06:28:47 GMT

Hi Erich Shiino

Even I also don't know what is that '36' in that. Also I don't wanted to display any values in it. I just wanted the background color start from StartDate and ends at the EndDate. So as to avoid displaying that numbers in the background I gave the same condition from the text as well.

For example:

In the above output in the green background, color of the number is also in green. So the numbers will not be visible in that.

But I guess there is something which that number does thats why they are using the chart. Still I couldn' t understand what is the number does there.