How to only filter and get a record on MIN date

[Oskido]

Hi,

I would like to make a filter to only get a record available based on MIN date

Here is an example:

I have a Date field and Skill field. I would like to make filter that will automatically retrieve the record on MIN date in this case "UWGLSERVSA"

[ChrisTX · Accepted solution]

Based on MIN date across the entire data set, or MIN date after grouping by another field?

Use a Summarize tool to find the MIN date

Then a Join tool:

  Left input anchor: your original data stream, with the detail records

  Right input anchor: gets data from the Summarize output anchor

  Join fields:  Left Date, Right MIN Date

Chris

[jdminton · Accepted solution]

You would do something like this

 

. 

[jdminton]

I see @ChrisTX described it while I was doing it! lol

[Oskido]

Thank you all for a quick response. 

What happens in a scenario where the MIN date is the same for two different records?

See record number 5 in the attached image.

Thanks..

[Raj]

@Oskido 
if you have a same min date for 2 records
you will have 2 records in output.

[jdminton]

Yes, @Raj is correct, you will get both. If you want to only get one, you could do another summarize after the join to take first of the skill, but that will only return the first in the list. If you are looking for other criteria, you would need to decide how you want to retrieve the record first.

[ChrisTX · Accepted solution]

@Oskido for your question: What happens in a scenario where the MIN date is the same for two different records?

The question back to you is:   what do you "want" to happen where the MIN date is the same for two different records?

If you want only one MIN value for the entire data set, don't use any Group By field in the Summarize tool.  The join by only the date field in the JOIN tool.

If you want a separate MIN value based on another field (like Skill), include the field in a Group By action in the Summarize tool.  The join by both fields in the JOIN tool.

Chris