Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I uploaded a table on Qlik Sense, called CarTable. Its columns are: Car Plate, Fuel Used, Day, Longitude, Latitude, Country. In short, for each car is reported the fuel consumption in each day that it has been used. It’s also reported the car location (Latitude, Longitude, Country) in which the measurement took place. The aim is to get on a map the last location of each car, according to a temporal filter (created with SenseDateRangePicker extension).
Example (invented data):
CarPlate Fuel Used Day Longitude Latitude Country
1020F 20 2017/08/21 8.9198249 45.3930345 Italy
1020F 12 2017/08/23 8.9355884 45.4196049 Italy
1020F 3 2017/08/30 9.1170112 45.5273901 Italy
… … … … … …
1020F 25 2017/09/29 9.1644466 45.3491890 Italy
1020F 10 2017/10/02 8.9807694 45.3948133 Italy
1234C 40 2017/09/21 9.0604222 45.3163479 Italy
1234C 20 2017/09/22 9.2852229 45.5134926 Italy
1234C 15 2017/09/30 8.9853044 45.3878548 Italy
… … … … … …
1234C 20 2017/10/03 9.1068999 45.3601810 Italy
… … … … … …
After uploading the table, in Editor Upload Data (Section automatically generated) appears the following code and I think that it is right:
[CarTable]:
LOAD
[CarPlate],
[FuelUsed],
[Day],
[Longitude],
[Latitude],
[Country],
APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Country])), '-') AS [CarTable.Country_GeoInfo],
GeoMakePoint([Latitude], [Longitude]) AS [Longitude_Latitude]
FROM [lib://Folder/CarData.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);
Longitude_Latitude is a new dimension created to position cars on a map.
In order to get on a map the last location of each car in accordance with the temporal filter, I have implemented the following two solutions (none of these work):
1) In Editor Upload Data (Section automatically generated) I have added the following code:
[LastTime]:
LOAD
CarPlate,
date(max("Day")) as LastDay
RESIDENT CarTable
GROUP BY CarPlate
;
Inner Join (CarTable)
LOAD *
RESIDENT LastTime
;
[LastPositionInfo]:
LOAD
[CarPlate] as Last_CarPlate,
[FuelUsed] as Last_FuelUsed,
[LastDay] as Last_Day,
[Country] as Last_Country,
[Latitude] as Last_Latitude,
[Longitude] as Last_Longitude,
APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Country])), '-') AS [LastPositionInfo.Country_GeoInfo],
GeoMakePoint([Latitude], [Longitude]) AS [Last_Location]
RESIDENT CarTable
WHERE "Day" = LastDay
;
The LastPositionInfo table is correctly created but it is not updated with the temporal filter.
For example: without a temporal filter the last measurement for a car with 1020F plate is 2017/10/02. The record corresponded to this day is reported in LastPositionInfo table. But if I put the temporal filter “from 2017/09/01 to 2017/09/30” its last measurement should become 2017/09/29 while in the table is still reported 2017/10/02 and its corresponding location.
2) I have created a new dimension (called LastLocation) with the following expression:
=aggr(peek(Longitude_Latitude),Devicesn)
However, this new dimension is empty and I don't know if it is possible to put it on a map.
Can someone help me? First, is it possible to do this in Qlik Sense? In case, which is the best way to do this?