Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Derek_G
Contributor II
Contributor II

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

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

2 Replies
Derek_G
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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

Derek_G_0-1630192369672.png

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.

Derek_G_1-1630192442063.png

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