Create 1=1 relation with calendar to show non-utilization?
I'm creating a vehicle utilization dashboard, but most importantly, need to show non-utilization. The data source only contains the dates that someone signed in as using a vehicle, so currently the only "non-utilization" I can show is if someone signed in, but then didn't input mileage (as it'd be 0). I have a calendar table on another database (diff server) and thought I could create a 1=1 relationship so every vehicle would have 365 entries....and then all the "null values" would be my new "non-utilization" count. I've accomplished this before in SQL if they're on the same server, but I honestly have no idea how to do this in QLIK.
I loaded the vehicle usage data and then loaded the calendar separately. I named both date columns the same knowing QLIK will make a connection, but it doesn't actually give me the desired result. I am new to QLIK, so not super savvy with QLIK Script or how to accomplish this. Any help is appreciated!
Forgive my ignorance, but i'm not sure that will work. Based on that example and others I found for interval match, it will match for items that exist. Essentially I need to "create" or "fill in" the blanks for the periods that don't exist.
For example, for the month of June if I only used a vehicle 6/1 - 6/14 (which would be 14 individual entries b/c it's always daily) AND entered mileage for all 14 days, my current query and calcs would show "0 unused days"...b/c there are 14 days of data and all have mileage. I need to show that there are truly 16 days of no utilization b/c no entries exist.
Using the rental example, you could do something like:
Day(MonthEnd(Month)) - Count(RentedDay)
But that would only work if you had at least one entry for the month. It sounds like you may have cases where you have no usage for the month. So continuing with your approach of joining every vehicle to every date, and adding a flag for used days:
Let vStartDate = MakeDate(2022,5,31); Calendar: LOAD *, Month(Date) as Month ; LOADDate($(#vStartDate) + RecNo()) as Date AutoGenerateMakeDate(2022,8,31) - $(#vStartDate) ;
Join(Calendar) LOAD * Inline [ VehicleId A B C ] ; LeftJoin(Calendar) LOAD *, 1 as Used Inline [ VehicleId, Date A, 6/1/2022 A, 6/2/2022 A, 6/3/2022 A, 7/1/2022 A, 7/15/2022 B, 7/7/2022 B, 7/8/2022 ] ;
So after digging around some more, I found a table with vehicle listing on the same server as the calendar table I was going to reference (I prefer this as it marks our holidays, fiscal week, year, etc opposed to using a date generator or other form). Anyway, this would have built a perfect 1=1 and then I could pull in utilization data from the 2nd server, but I get an error in QLIK with the below:
WITH DATES as ( SELECT cal_FY as FY, cal_wk as Week, cal_dow as DOW, CAST(cal_date as DATE) as Cal_Date
FROM database.calendar where cal_fy > 2021 and cal_month = 7 and cal_dow <> 'sun' and cal_hol is null )
SELECT vehicle_id, DATES.Cal_Date FROM database.vehicles FULL OUTER JOIN DATES ON 1=1
The following error occurred:
Connector reply error: Executing non-SELECT queries is disabled. Please contact your system administrator to enable it.
This may be limited to our system admin and I can tell you now I don't have the clout to get it changed. So is my only other option to create a temp table, load dates, create a temp table, load vehicles, then do a JOIN on the two tables in QLIK Script for my 1=1? Would I then tie in the utilization from a 3rd temp table? I'm not super familiar with QLIK, so would it look something like this?
[DATA]: LOAD vehicle_id as vehicle
JOIN (DATA) LOAD
Cal_Date as Date
Resident xDATES; Drop Table xDATES;
Drop Table xVEHICLE;
[UTILIZATION]: Load vehicle, Date Resident DATA;
LEFT JOIN Load vehiclenumber as vehicle, beginodometer, endodometer, eventenddate as Date, location
Resident xUTILIZATION; Drop Table DATA; Drop Table xUTILIZATION;
Yes, In Qlik, With Joins, you work at a time only on two tables, not more. So if you need another temp table, do use it. Try, and see what happens. You are going in right direction That is the best way 👍. Just be careful about the joins so that you don`t get a cartesian join when you don`t want it, so be careful about the fieldnames being used for join, and verify twice that these exist in both the tables.
The input to a Qlik LOAD can be an SQL statement as a "preceding load", and SQL statements can also stand alone in your Qlik script. So I would think your Qlik script would look like this. I don't think you need any temp tables. I didn't see a SQL statement for your utilization data so I just put a placeholder at the end.
Calendar: SQL SELECT cal_FY as FY, cal_wk as Week, cal_dow as DOW, CAST(cal_date as DATE) as Cal_Date FROM database.calendar where cal_fy > 2021 and cal_month = 7 and cal_dow <> 'sun' and cal_hol is null ;
Join(Calendar) SQL SELECT vehicle_id FROM database.vehicles ;
Left Join(Calendar) LOAD *, 1 as Used ; SQL Select foo from your utilization table;