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
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
Create Master calendar from the date field Load_Date from main table
and then use month from master calendar in your chart
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
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);
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.
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
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.
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.
I thought licenseplate is unique key
can you tell me how main table and fueltable link with each other
Can you show the expression you use?