topic Re: Create 1=1 relation with calendar to show non-utilization? in App Development

Create 1=1 relation with calendar to show non-utilization?

drew61199 — Fri, 05 Aug 2022 14:16:05 GMT

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!

Re: Create 1=1 relation with calendar to show non-utilization?

rwunderlich — Fri, 05 Aug 2022 14:38:33 GMT

Take a look at this downloadable example for one approach to this problem.

Qlikview Cookbook: Count Days In A Transaction Using Intervalmatch https://qlikviewcookbook.com/recipes/download-info/count-days-in-a-transaction-using-intervalmatch/

-Rob

Re: Create 1=1 relation with calendar to show non-utilization?

drew61199 — Fri, 05 Aug 2022 17:10:35 GMT

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.

Re: Create 1=1 relation with calendar to show non-utilization?

rwunderlich — Fri, 05 Aug 2022 22:38:24 GMT

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
;
LOAD Date($(#vStartDate) + RecNo()) as Date
AutoGenerate MakeDate(2022,8,31) - $(#vStartDate)
;

Join(Calendar)
LOAD * Inline [
VehicleId
A
B
C
]
;
Left Join(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
]
;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: Create 1=1 relation with calendar to show non-utilization?

vidyutverma — Sat, 06 Aug 2022 08:14:56 GMT

Hi,

If you have work with SQL, then you are OK with Joins. So Lets first get 365 (or 366 in case of leap year) rows for each vehicle, having every date of the year. To do this, use following steps:

1. Create a table `Vehicles` with each unique vehicle ID

2. Create a table with dates for the whole year ( Using Autogenerate and Makedate )

3. Do a cartesian join of these two tables to get dates for each vehicle.

After that doing another left join with your transactions tabkle should give you what you want.

Re: Create 1=1 relation with calendar to show non-utilization?

drew61199 — Mon, 08 Aug 2022 20:51:52 GMT

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

Resident xVEHICLE;

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;

Re: Create 1=1 relation with calendar to show non-utilization?

vidyutverma — Mon, 08 Aug 2022 21:35:54 GMT

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.

Re: Create 1=1 relation with calendar to show non-utilization?

rwunderlich — Tue, 09 Aug 2022 15:15:30 GMT

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;

-Rob