Filter Coda Table Row with a Minimum Value of a Specific Column

1

I want to filter a table to have unique entries. They must have unique juris_key and pub_key. If they have the same juris_key and pub_key, only show rows with the minimum effective_date. How to do that using a formula ? below are some of the entries:

name | juris_key | pub_key | Effective Date|
name 1 | alabama | ashrae_901_2013 | July 1, 2016|
name 2 | alabama | ashrae_901_2013 | July 1, 2022|
name 3 | alabama | int_building_code_2009 | September 1, 2010|
name 4 | alabama | int_building_code_2015 | July 1, 2016|
name 5 | alabama | int_building_code_2021 | July 1, 2022|

After filtering it out correctly, it must only show the following rows:

name | juris_key | pub_key | Effective Date|
name 1 | alabama | ashrae_901_2013 | July 1, 2016|
name 3 | alabama | int_building_code_2009 | September 1, 2010|
name 4 | alabama | int_building_code_2015 | July 1, 2016|
name 5 | alabama | int_building_code_2021 | July 1, 2022|

2

Hi @Alyssa_Gono :blush: !

There might be different ways to do this but this Filter() formula seems to work :blush:

Table.Filter(
  juris_key = thisRow.juris_key 
  AND pub_key = thisRow.pub_key).MinBy([Effective Date]).Contains(thisRow)

The Filter() creates a list of rows where CurrentValue.juris_key = thisRow.juris_key and CurrentValue.pub_key = thisRow.pub_key.
Then, MinBy() looks into the CurrentValue.[Effective Date] from the returned lists of rows and only keeps the row where the effective date is the minimum one and that row is then compared to thisRow to filter it out :blush:

Effective date minimum value
Effective date minimum value2800×1230 356 KB

Note that I used a connected view in the sample below to test this :blush: …

I hope this helps :innocent:

1 Like
3

Wow, such a great help! Thanks!

4

My pleasure @Alyssa_Gono :grin: !

Glad to know this seems to do what you were looking for :blush: !

1 Like
5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.