topic Re: Aggregation not Works in QlikView

Aggregation not Works

r_pinzke — Sun, 22 Jul 2018 08:08:37 GMT

Hello,

I have a question.

I want to calculate the load of different rooms.

In addition I have two Excel files, which I reconstructed here with Inline Tables.

My problem is that I do not see the total number of hours of capacity because the record lacks a tag (Thursday). But even this day had a capacity that was not used. I've tried all aggregation, but it never gets out the number 5.

Here are my questions:

Is it possible to have this missing tag displayed?

How can I see the correct number of 5 hours for each room?

Best regards

Rico

Here is the script:

--------------------

Temp1:
CrossTable(Weekday,Capacity,3)
Load * Inline [
Team, House, Room, Mon,Tue, Wed, Thu, Fri, Sat, Sun
A, A, 1, 1, 1, 1,   1,   1, 0, 0,
A, A, 2, 1, 1, 1,   1,   1, 0, 0
];

Capacity:
Load
Team&'-'&House&'-'&Room&'-'&Weekday as Key,
Team&'-'&House&'-'&Room&'-'&Weekday as NoKey,
Capacity resident Temp1;
DROP Table Temp1;

Temp2:
Load * Inline [
Dates, Team, House, Room, Hours
05.03.2018, A, A, 1, 1,
06.03.2018, A, A, 1, 1,
07.03.2018, A, A, 1, 1,
09.03.2018, A, A, 1, 1,
10.03.2018, A, A, 2, 1,
];

Data:
Load *,
Team&'-'&House&'-'&Room&'-'&weekday(Dates) as Key,
date(Dates) as Date Resident Temp2;
Drop Table Temp2;

LET vMinDate = Num('05.03.2018');
LET vMaxDate = NUm('10.03.2018');


TempCalendar:
LOAD
   $(vMinDate) + IterNo() - 1 as Num,
   Date($(vMinDate) + IterNo() - 1) as TempDate
AutoGenerate
   1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Calendar:
LOAD
   TempDate as Date,
   Week(TempDate) as Week,
   Year(TempDate) as Year,
   Month(TempDate) as Month,
   Day(TempDate) as Day,
   Weekday(TempDate) as WeekDay

Resident TempCalendar Order By TempDate ASC;

DROP Table TempCalendar;

Re: Aggregation not Works

sunny_talwar — Mon, 23 Jul 2018 15:21:41 GMT

What are the expressions that you are using?

Re: Aggregation not Works

r_pinzke — Mon, 23 Jul 2018 16:15:13 GMT

Hallo,

I have used different expressions, but this not working.

//Aggr(sum(Capacity), WeekDay,Week,Room,House,Team)
//Aggr(sum(Capacity), Weekday)
//Aggr(sum(Capacity), Room)
//Aggr(sum(Capacity), Team)
//sum( {1<Room>} Capacity)
//sum(Capacity)

I don't get the summary of numbers (Capacity) in weekdays for Room, House or Team.

Load * Inline [
Team, House, Room,    Mo, Di, Mi, Do, Fr, Sa, So
A,     A,      1,       1, 1, 1, 1, 1, 0, 0,
A,     A,      2,       1, 1, 1, 1, 1, 1, 0
];

Have You a other Idea?

Rico

Re: Aggregation not Works

sunny_talwar — Mon, 23 Jul 2018 16:37:23 GMT

I might be failing to understand what you need here.... would you be able to provide the expected output?

Re: Aggregation not Works

r_pinzke — Mon, 23 Jul 2018 17:01:40 GMT

Hallo,

Thank you for your patience. I just want to show the sum of hours for the days of the week, but I can not get that.

I Have bald this in Photoshop

This is the table for Capacity:

Team, House, Room, Mo, Di, Mi, Do, Fr, Sa, So
A, A, 1, 1, 1, 1, 1, 1, 0, 0, // 5 Hours Capacity for this Team, House, Room
A, A, 2, 1, 1, 1, 1, 1, 1, 0 // 6 Hours for ....
]

With best regards.

Rico

Re: Aggregation not Works

sunny_talwar — Mon, 23 Jul 2018 17:18:18 GMT

This might move you in the right direction.... somewhat

Script

Temp1:

CrossTable(Weekday,Capacity,3)

LOAD * INLINE [

Team, House, Room, Mon, Tue, Wed, Thu, Fri, Sat, Sun

A, A, 1, 1, 1, 1, 1, 1, 0, 0

A, A, 2, 1, 1, 1, 1, 1, 1, 0

];

Capacity:

LOAD Team&'-'&House&'-'&Room&'-'&Weekday as Key,

Team&'-'&House&'-'&Room&'-'&Weekday as NoKey,

Team,

House,

Room,

Weekday,

Capacity resident Temp1;

DROP Table Temp1;

Temp2:

LOAD * INLINE [

Dates, Team, House, Room, Hours

05.03.2018, A, A, 1, 1

06.03.2018, A, A, 1, 1

07.03.2018, A, A, 1, 1

09.03.2018, A, A, 1, 1

10.03.2018, A, A, 2, 1

];

Data:

Load *,

Team&'-'&House&'-'&Room&'-'&weekday(Dates) as Key,

date(Dates) as Date Resident Temp2;

Drop Table Temp2;

LinkTable:

LOAD Key,

Team,

House,

Room,

Weekday

Resident Capacity;

Concatenate (LinkTable)

LOAD Key,

Team,

House,

Room,

Text(WeekDay(Dates)) as Weekday

Resident Data;

DROP Fields Team, House, Room, Weekday from Capacity;

DROP Fields Team, House, Room, Weekday from Data;

LET vMinDate = Num('05.03.2018');

LET vMaxDate = NUm('10.03.2018');

TempCalendar:

LOAD

$(vMinDate) + IterNo() - 1 as Num,

Date($(vMinDate) + IterNo() - 1) as TempDate

AutoGenerate

1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Calendar:

LOAD

TempDate as Date,

Week(TempDate) as Week,

Year(TempDate) as Year,

Month(TempDate) as Month,

Day(TempDate) as Day,

Weekday(TempDate) as WeekDay

Resident TempCalendar Order By TempDate ASC;

DROP Table TempCalendar;

Re: Aggregation not Works

r_pinzke — Mon, 23 Jul 2018 18:36:50 GMT

Many many thanks. That works great.

The linked table is great.

Many many thanks.

And thank you for your patience and effort.

Rico