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

# Calculation to fill in missing timestamps and lat/longs

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

• ###### 1. Re: Calculation to fill in missing timestamps and lat/longs

Hi Kathleen,

Here you go:

You need to self join your data set using calculation join on 1=1

Then use the below formula to create a Boolean T|F field:

{fixed [Datetime],[Ship Name (ship.csv1)] : max(if [Datetime (ship.csv1)]<=[Datetime] then [Datetime (ship.csv1)] end)}=[Datetime (ship.csv1)]

Put it on filter shelf and choose TRUE.

Attaching the workbook for your reference.

Hope it helps.

Thanks,

Ankit Bansal