0 Replies Latest reply: Oct 5, 2017 5:11 AM by Federica Mutti RSS

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

    Federica Mutti

      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?