Combining 2 datefields into 1

[pascaldijkshoor]

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

[arvind1494]

Create Master calendar from the date field Load_Date  from main table

and then use month from master calendar in your chart

[arvind1494]

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);

// Master calendar script:

CalendarMaster:

LOAD

    Date(Load_Date) AS Load_Date,

    Date(JLoad_Date) AS CalendarDate,

    Year(Load_Date) AS Year,

    'Q' & Ceil(Month(Load_Date) / 3) AS Quarter,   

    Month(Load_Date) As Month,

    Day(Load_Date) As Day,

    Week(Load_Date) As Week;

Load Date(MinDate + IterNo() -1 ) AS Load_Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(Load_Date) AS MinDate,

    Max(Load_Date) AS MaxDate

RESIDENT MainTable;

Now use this month as a dimension in your chart

[Anonymous]

I do not know what you want in the chart at the end, but you can rename field  "FuelDate" to Load_Date and concotenate FuelTable to  MainTable. So you get one table with one DateField

MainTable:

LOAD .................................;

Concatenate (MainTable)

FuelTable:

LOAD

    "FuelDate" as Load_Date ,

    "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);

[pascaldijkshoor]

Thanks for your reply. In your solution, how is the fuel date linked to the master calendar? With the master calendar month field, i would get the revenue per month, but for the fuel costs i would get the total fuel costs, because the costs are not linked to the master calendar dates.

[arvind1494]

Because Main table and FuelTable are associated through the field  LicensePlate

And Main table linked to calender table through the Load_Date

IF you look at the table viewer you will see the associations

[pascaldijkshoor]

Yes but the licenseplate is no unique key, so the same licenseplate will occur in every load_date month. This means Qlik does not know in which load_date month specific fuel costs are made.

[pascaldijkshoor]

Thanks for your reply but this solution does not work. I am getting zero values if i have load_date as a dimension and fuel costs as a measure.

[arvind1494]

I thought licenseplate  is unique key

can you tell me how main table and fueltable link with each other

[Anonymous]

Can you show the expression you use?