Sorry if this seems so basic, but I'm developing a vehicle utilization dashboard and was able to calculate "non usage" in the frontend, but once I have more than a month's worth of data loaded, it makes the dashboard very slow. Ultimately I would like to convert this to work in the load script (as a 1,0 or Y,N flag?! ) and I can simply count / sum that in the frontend.
The main data source only has entries if a vehicle was used. In order to find non-usage, I had to create a table by cartesian joining all valid vehicle numbers with valid dates and then I LEFT JOIN in the usage data. So if a vehicle was used M,W, I would get a 4 for "non-usage" (T,Th,F,S....Sun is a non-valid day).
My set analysis is aggregating by date and vehicle as that is how I am rolling up the data. "In_Shop" is a flag I create in the SQL pull (CASE WHEN statement) b/c if a vehicle was designated as "in the shop for repairs", then I don't want that to count against a facility for the vehicle not being utilized.
I tried a simple IF((endmileage-beginmileage)=0,1,0) and that works, but ONLY for those vehicles where end-begin = 0 (super rare). For vehicles with no entries, I don't get a count and this is a major issue.
I'm not going to bother with including the SQL pulls, but here is my QLIK script for the cartesian and left join if it helps at all....
NoConcatenate [DATA]: Load * RESIDENT [xVEHICLES]; JOIN Load * RESIDENT [xDATES]; DROP TABLE [xVEHICLES]; DROP TABLE [xDATES];
LEFT JOIN
LOAD financenumber as utilized_finance, routetypecode as Route_Type, beginodometer as Begin_Mileage, endodometer as End_Mileage, eventenddate as Date, eventstarttime as Start_Time, eventendtime as End_Time, vehiclenumber as vehicle, endodometer - beginodometer as Utilization, in_shop as In_Shop,
I would use a bit different approach which concatenates the missing data to the existing ones, for example with something like:
t1: load date from Calendar; join(t1) load vehicles from Vehicles; // creates the cartesian
t2: load *, date & '|' & vehicle as ExistsKey, 'Facts' as Source fromFactData; concatenate(t2) load date, vehicle, 0 as Mileage, 'none' as xyz, ..., 'Populated' as Source from t1 where not exists(ExistsKey, date & '|' & vehicle);
Benefits would be that you could give all missing records a real value by setting the fields to 0 or 'none' instead of NULL which would the join return (of course you may add a following load and checking the exists of values and adjusting the values appropriate - but it would be an additionally step) and you could set a Source information to be able to differentiate between the existing and populated ones.
