Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi people, I would like to combine 2 datefields into 1 datefield. I will explain the situation below:
I am loading 2 tables. The main table contains the date field "Load_Date". This datefield refers to the loading date of a shipment number. The main table also contains which Asset transported the shipment. I am calculating the total revenue per asset per month based on the main table.
The second table is my fuel costs table. This table contains the licenseplate, the fuel costs and FuelDate, so i can calculate the fuel costs per asset per month based on this table.
The main table and fuel table are linked together based on the license plate of the asset. So i know per asset how much revenue is on it and what the fuel costs are. Only i have got 2 different date fields, the Load_Date for the main table and the FuelDate for the fuel costs table. The different date fields make it impossible to create a chart with revenue and fuel costs per asset per month.
How can i combine both date fields so i can create this chart?
My current script is posted below:
LIB CONNECT TO 'Chainware11g';
MainTable:
LOAD cuactk as Key,
curem1 as LicensePlate,
cuasst as AssetType,
curem1 as Activity_Asset_Details,
jrasst as AssetNumber,
tsrido as Tripnumber,
cuacty as Activity,
tsdist as Activity_Distance,
tsuitd as Load_Date,
trip_loadindex,
shipment_loadindex,
dosvlg as ShipmentNumber;
SELECT
cuacta. "cuasst",
cuacta. "curem1",
cuacta. "jrasst",
cuacta. "cuactk",
cuactt. "cuactk",
cuacts. "cuactk",
cuactt. "cuacty",
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 cuacta. "cuasst" = 'driving unit'
AND cuactt. "cuacty" = 'Driving'
AND tsdsmd."tsuitd" >= to_date('01-01-2018', 'DD-MM-YYYY');
FuelTable:
LOAD
"FuelDate",
"License Plate Short" as LicensePlate,
"Product Code",
"Product Name",
"Net Euro Amount"
FROM [lib://Data_Qlik /Shell Fuel Report.xlsm]
(ooxml, embedded labels, table is FuelReport);
Thanks in advance
for month i used Month(Load_Date) and for the fuel costs i used sum(Net Euro Amount). It gave me results for several months but for februari and march it showed 0.
The tables are linked on Licenseplate. Every truck has it's own unique license plate, only every truck will transport multiple shipments per month, so the licenseplate has multiple revenue lines per month. Also every truck will fuel multiple times per month, so the licenseplate has multiple fuel lines per month.
I found a way. Just created in the main table the field:
Month (Load_Date) as CombinedMonth
and in the fuel table the field:
Month (FuelDate) as CombinedMonth
This creates a synthetic key but i guess this is not bad in my case. The results are correct anyway.
Thanks for your help