Hello People,
I am loading 2 tables, the main table and a connection costs table. The main table contains all shipments, and the connection costs table only contains shipments with a connection. The tables are linked on shipment number. I am using the connection costs for formulas in my app. The issue is that most shipments don't have connection costs, so they have no value in that field. This means that the result of the formula will be no value as well.
Is there a way to give all shipments that have no value for connection costs a 0 instead of no value?
Currently I am using the following script:
LIB CONNECT TO 'Chainware11g;
Main Table
LOAD cuactk as Key,
curem1 as LicensePlate,
cuasst as Assettype,
curem1 as Activity_Asset_Details,
cuconn as ConnectionLane,
jrasst as Asset_Number,
tsrido as Tripnumber_CW,
cuacty as Activity,
tsaasd as Start_Date,
tsaaed as End_Date,
tsdist as Activity_Distance,
tsuitd as Load_Date,
tsstts as CW_StatusCode,
dosvlg as Shipment_Number_Linked,
Left(dosvlg,3) AS Department_Code,
if (jrasst>99999,'Charter','No Charter') as IsCharter;
SELECT
cuacta. "cuasst",
cuacta. "curem1",
cuacta. "jrasst",
cuacta. "cuactk",
cuactt. "cuactk",
cuacts. "cuactk",
cuactt. "cuacty",
cuactt. "cuconn",
cuactt. "tsaasd",
cuactt. "tsaaed",
cuactt. "tsdist",
cuactt. "tsloai" as "trip_loadindex",
cuactt. "tsrido",
cuacts. "dosvlg",
tsdsmd. "dosvlg",
tsdsmd. "tsstts",
tsdsmd. "tsuitd",
tsdsmd. "tsloai" as "shipment_loadindex"
FROM "ICJDR"."cuf_cuactt" cuactt,
"cuf_cuacta" cuacta,
"cuf_cuacts" cuacts,
"cef_tsdsmd" tsdsmd
WHERE
cuactt. "cuactk" = cuacta. "cuactk"
AND cuactt. "cuactk" = cuacts. "cuactk"
AND cuacts. "dosvlg" = tsdsmd. "dosvlg"
AND "cuasst" = 'driving unit'
AND cuactt. "cuacty" = ('Driving')
AND tsdsmd."tsuitd" >= to_date('01-01-2018', 'DD-MM-YYYY');
ConnectionMap:
Mapping LOAD
"Connection",
"Connection Costs"
FROM [lib://Data_Qlik/Connections.xlsx]
(ooxml, embedded labels, table is Connections);
LIB CONNECT TO 'Chainware11g;
Connection Costs Table
load
dosvlg as Shipment_Number_Linked,
"Connection",
shipment_loadindex/trip_loadindex*(ApplyMap('ConnectionMap',"Connection",0)) as ShipmentConnectionCosts;
Select
cuacta. "cuactk",
cuactt. "cuactk",
cuacts. "cuactk",
cuactt. "cuacty",
cuactt. "cuconn" as "Connection",
cuacta. "cuasst",
cuactt. "tsloai" as "trip_loadindex",
cuacts. "dosvlg",
tsdsmd. "dosvlg",
tsdsmd. "tsloai" as "shipment_loadindex"
from "ICJDR"."cuf_cuactt" cuactt,
"cuf_cuacta" cuacta,
"cuf_cuacts" cuacts,
"cef_tsdsmd" tsdsmd
where
cuactt. "cuactk" = cuacta. "cuactk"
AND cuactt. "cuactk" = cuacts. "cuactk"
AND cuacts. "dosvlg" = tsdsmd. "dosvlg"
AND cuacta. "cuasst" = 'driving unit'
AND cuactt. "cuacty" IN ('Connection')
AND tsdsmd."tsuitd" >= to_date('01-01-2018', 'DD-MM-YYYY');
Thanks in advance
