1 Reply Latest reply on Aug 28, 2018 8:17 AM by Ankit Bansal

    Calculation to fill in missing timestamps and lat/longs

    Kathleen Callaghan

      Hi there,

       

      I have a shipping dataset where each row has a unique combination of latitude and longitude coordinates, a timestamp at which those coordinates were taken, and the name of the ship at that time and location.  The problem I'm having is that each ship's lats/longs were taken at different times throughout the day.  What I want to do is for each unique datetime in my dataset, record the latitude and longitude for each ship if one was recorded at that time, otherwise record the most recent latitude and longitude.  So basically I'd have an entry for every ship at each datetime with latitude and longitude values.  I'm not sure if there is a way to do this in Tableau or if this is something I'd have to do to my dataset before connecting to Tableau. 

       

      Here is an example of what I have:

       

      Datetime                              Ship Name          Latitude          Longitude

      4/28/2014 12:13:44 AM        ADMIRAL            33.56                44.61

      4/28/2014 12:13:56 AM        OST                     44.62               33.53

      4/28/2014 12:17:43 AM        ADMIRAL            34.56                44.63

      4/28/2014 12:19:56 AM        OST                     44.78               33.53

       

       

       

      And this is an example of what I want:

       

       

      Datetime                              Ship Name          Latitude          Longitude

      4/28/2014 12:13:44 AM        ADMIRAL            33.56                44.61

      4/28/2014 12:13:44 AM       OST                     most recent      most recent

       

      4/28/2014 12:13:56 AM        OST                     44.62               33.53

      4/28/2014 12:13:56 AM        ADMIRAL             33.56               44.61 

       

      4/28/2014 12:17:43 AM        ADMIRAL            34.56                44.63

      4/28/2014 12:17:43 AM        OST                     44.62               33.53 

       

      4/28/2014 12:19:56 AM        OST                     44.78               33.53

      4/28/2014 12:19:56 AM        ADMIRAL            34.56                44.63      

       

      Thank you in advance.