Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Aggregation not Works

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.

screenshot.jpeg

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,
C
apacity 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;  


1 Solution

Accepted Solutions
Highlighted

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;


Capture.PNG

View solution in original post

6 Replies
Highlighted

What are the expressions that you are using?

Highlighted
Contributor II
Contributor II

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

Highlighted

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

Highlighted
Contributor II
Contributor II

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 ....
]

screen1.jpeg

With  best regards.

Rico

Highlighted

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;


Capture.PNG

View solution in original post

Highlighted
Contributor II
Contributor II

Many many thanks. That works great.

The linked table is great.

Many many thanks.

And thank you for your patience and effort.

Rico