Need to aggregate data from two tables (with Date) via scripting for data load

[Derek_G]

Hello,

I have created the below measure to quantify the number of vehicles that are on rent at any point in time based on the rental start and end date.  Unfortunately the performance of this formula is too slow (only about 400k lines currently but will increase significantly when I focus on primary country) as it takes about 10 seconds for a simple filter criteria to refresh. 

Count({<[Reservation Status]-={'CC','CP','NS'}>}if([Calendar.autoCalendar.Date]>=[Projected Checkout date] AND [Calendar.autoCalendar.Date]<[Projected Checkin date],[Calendar.autoCalendar.Date],))

+

Count({<[Reservation Status]-={'CC','CP','NS'}>}if([Calendar.autoCalendar.Date]=[Projected Checkout date] AND [Calendar.autoCalendar.Date]=[Projected Checkin date],[Calendar.autoCalendar.Date],))

+

if([Projected Checkout date]=[Projected Checkin date],0,count({<[Reservation Status]-={'CC','CP','NS'}>}if([Calendar.autoCalendar.Date]=[Projected Checkin date] AND Interval([Checkin Time]-[Checkout Time],'hh:mm')>1/48,[Calendar.autoCalendar.Date],)))

So I'd thought it would be more efficient to aggregate the data somewhat during the data loading process.

So far I have created the below script in an attempt to just create a simple part of the measure (middle) to understand the syntax and how Qlik works, but I am unable to 'find' the field in Table1 'CalendarTable' to summarise the number of rentals for each day, based on grouping (which will increase once I understand the logic:

Calendartable:
Load
CalendarDate as [CDate]
Resident [Calendar];

Left join (Calendartable)
[Reservation Days v4]:
Load
[Checkout station Name] as [CO Station Name4],
//[Projected Checkout date] as [Proj_CO_date],
//[Projected Checkin date] as [Proj_CI_date],
Count([Reservation number]) as [Res_Days4]
Resident [Reservations]
Where
[Projected Checkout date]=[Projected Checkin date]
and
([Reservation Status]<>'CC' or [Reservation Status]<>'NS')
Group By [Checkout station Name];

Any help is greatly appreciated.

Thanks.

Derek

[Derek_G]

Sorry, minor typo.  The attempted script should look like:

Calendartable:
Load
CalendarDate as [CDate]
Resident [Calendar];

Left join (Calendartable)
[Reservation Days v4]:
Load
[Checkout station Name] as [CO Station Name4],
//[Projected Checkout date] as [Proj_CO_date],
//[Projected Checkin date] as [Proj_CI_date],
Count([Reservation number]) as [Res_Days4]
Resident [Reservations]
Where
[Projected Checkout date]=[Cdate]
and
([Reservation Status]<>'CC' or [Reservation Status]<>'NS')
Group By [Checkout station Name];

[Derek_G]

To add further context:  The below screenshot is the output of the current measure.

But to improve performance I'd like to create an aggregated table in the data loading process that is formatted something like the below, so I can just SUM the 'Bookings' by the relevant grouped fields.

There is no current link/join between 'Reservations' table and the 'Calendar' table.

I've tried all sorts of combinations of referencing but obviously not the correct one.

Thanks in advance.

Derek