Qlik Community

Ask a Question

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help required in creating aggregation table!

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:

A1

A10

StartDate

EndDate

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

Expression:

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

14 Replies
Partner
Partner

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 ;



Not applicable

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.

Partner
Partner

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,
Not applicable

Hi

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.

Not applicable

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.

Not applicable

Hi

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!

Partner
Partner

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,

Not applicable

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.

Partner
Partner

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.