Aggregation not Works

[r_pinzke]

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;  

[sunny_talwar]

What are the expressions that you are using?

[r_pinzke]

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

[sunny_talwar]

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

[r_pinzke]

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

[sunny_talwar · Accepted solution]

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;

[r_pinzke]

Many many thanks. That works great.

The linked table is great.

Many many thanks.

And thank you for your patience and effort.

Rico