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: 
Not applicable

Put a last position of a car on a map in accordance with a temporal filter

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?





0 Replies