Transfer calculation from frontend to load script

drew61199 — Fri, 09 Sep 2022 20:42:51 GMT

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.

Here's the formula I'm hoping to convert:

Count(AGGR(IF(SUM(Utilization)=0,vehicle),vehicle,Date)) - Count({<In_Shop={"YES"}>}Date)

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,

Resident xAVUS;
Drop Table xAVUS;

Re: Transfer calculation from frontend to load script

marcus_sommer — Mon, 12 Sep 2022 13:08:18 GMT

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.

- Marcus

topic Transfer calculation from frontend to load script in App Development

Transfer calculation from frontend to load script

Re: Transfer calculation from frontend to load script