Create filtered table based on max value

[tom1tas]

Hello All, appreciate your help in this.
I am trying to filter my table, by getting one company value for each date, there are some cases where one employee worked for two companies during the same day and I need to filter the data for the one that has a higher time duration.

Conditions For the filter

• Keep Company with the higher duration for each day
• Keep all dates available 
• In case duration for both companies is exactly the same during the same day, keep wichever butt keep only one daily.  

Current Table

Reported Date	Employee	Employee ID	Total Duration	Company
12/1/2022	Andy	123	8	Cars Company
12/1/2022	Andy	123	7	Mobile Company
12/1/2022	Sam	321	8	Chicken Company
12/1/2022	Sam	321	8	Cars Company
12/3/2022	Dan	456	5	Soups Company
2/2/2023	Sam	123	8	Soups Company

Expected filtered Table

Reported Date	Employee	Employee ID	Total Duration	Company
12/1/2022	Andy	123	8	Cars Company
12/1/2022	SAm	321	8	Chicken Company
12/3/2022	Dan	456	5	Soups Company
2/2/2023	Sam	123	8	Soups Company

The goal is to get unique company values for each date employee id on each date available. 

[serpiva64 · Accepted solution]

Hi,

you have to group by

Expand all rows

add 2 conditional columns

and finally filter all Yes

(you obtain the company in alphabetical order, if you want the first you have to add an index before)

You can take a look at the attached file

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

[tom1tas]

Its removing some of my dates, I need to keep all the dates but it seems when it encounters the same duration for the same date and with a different company  it removes the date completely. Please Help. Wondering if this is related to not adding the index, could you explain that step by step, thanks in advance.

Current Status

Employee Id	Company	Date	Duration
123	Chicken Company	1/1/2023	4
123	Box Company	1/1/2023	4
321	Box Company	1/2/2023	8

Error  out of query:

Employee Id	Company	Date	Duration
321	Box Company	1/2/2023	8