No values as 0 with linked tables

pascaldijkshoor — Fri, 03 Aug 2018 14:30:35 GMT

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

Re: No values as 0 with linked tables

petter — Mon, 06 Aug 2018 05:01:26 GMT

Even if the detail level of shipment costs returns a "no value" / null you can make sure that it returns 0 instead by always using an aggregation function like Sum() in the expressions (formulas). It is advisable to ALWAYS use aggregations when calculating both for this reason and other reasons. Use Aggregation Functions!

Re: No values as 0 with linked tables

pascaldijkshoor — Wed, 08 Aug 2018 11:40:10 GMT

Thanks!

topic No values as 0 with linked tables in App Development

No values as 0 with linked tables

Re: No values as 0 with linked tables

Re: No values as 0 with linked tables