Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pascaldijkshoor
Creator
Creator

Combining 2 datefields into 1

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

12 Replies
pascaldijkshoor
Creator
Creator
Author

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.

pascaldijkshoor
Creator
Creator
Author

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.

pascaldijkshoor
Creator
Creator
Author

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