Data expression to pull out most recent survey results

168
0
11-11-2021 03:33 AM
Labels (1)
KatieQuantrell
New Contributor II

Hi,

I am trying to create a data expression to pull out a dataset that just contains the most recent survey results for several survey stations at various locations. My main feature layer contains fields for location ("Location", string), survey station id ("StationID" field, string), survey date ("SurveyDate" field, date) and survey result ("CheckResults", string (either 'Positive' or 'Negative')).

This is similar to a previous query raised on this forum "Data Expressions using GroupBy and Filter" but the solution to this doesn't work in this case unfortunately as all survey stations aren't necessary surveyed on the same day each time. So the most recent record for station A1 may have a different date to the most recent record for station A2 for example. 

An example of my data is shown below:

Location

StationID

SurveyDate

CheckResults

A

A1

01/10/2019

Negative

A

A2

12/09/2021

Positive

A

A3

05/06/2021

Negative

B

B1

05/06/2021

Positive

B

B2

28/06/2020

Negative

A

A1

22/10/2016

Negative

A

A2

15/09/2020

Negative

A

A3

03/05/2019

Positive

B

B1

04/05/2019

Negative

B

B1

06/04/2018

Positive

 

What I would like to return: 

Location

StationID

SurveyDate

CheckResults

A

A1

01/10/2019

Negative

A

A2

12/09/2021

Positive

A

A3

05/06/2021

Negative

B

B1

05/06/2021

Positive

B

B2

28/06/2020

Negative

 

Any help on how to do this would be really appreciated.

Many thanks, 

0 Kudos
0 Replies