topic Re: Combining 2 datefields into 1 in App Development

Combining 2 datefields into 1

pascaldijkshoor — Tue, 14 Aug 2018 10:01:52 GMT

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

Re: Combining 2 datefields into 1

arvind1494 — Tue, 14 Aug 2018 10:12:01 GMT

Create Master calendar from the date field Load_Date from main table

and then use month from master calendar in your chart

Re: Combining 2 datefields into 1

arvind1494 — Tue, 14 Aug 2018 10:19:10 GMT

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

Re: Combining 2 datefields into 1

Anonymous — Tue, 14 Aug 2018 10:33:39 GMT

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

Re: Combining 2 datefields into 1

pascaldijkshoor — Tue, 14 Aug 2018 10:58:26 GMT

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.

Re: Combining 2 datefields into 1

arvind1494 — Tue, 14 Aug 2018 11:03:58 GMT

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

Re: Combining 2 datefields into 1

pascaldijkshoor — Tue, 14 Aug 2018 11:23:21 GMT

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.

Re: Combining 2 datefields into 1

pascaldijkshoor — Tue, 14 Aug 2018 11:24:04 GMT

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.

Re: Combining 2 datefields into 1

arvind1494 — Tue, 14 Aug 2018 11:31:18 GMT

I thought licenseplate is unique key

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

Re: Combining 2 datefields into 1

Anonymous — Tue, 14 Aug 2018 11:49:45 GMT

Can you show the expression you use?

Re: Combining 2 datefields into 1

pascaldijkshoor — Tue, 14 Aug 2018 12:06:26 GMT

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.

Re: Combining 2 datefields into 1

pascaldijkshoor — Tue, 14 Aug 2018 12:32:16 GMT

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.

Re: Combining 2 datefields into 1

pascaldijkshoor — Tue, 14 Aug 2018 12:44:14 GMT

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