Conditional Filtering Based on Hierachy

[np123]

Hi everyone, 

I need some help regarding conditional filtering. I tried to look for solutions but was not able to find any. 

Here's a sample data:

input:                                                                                                  output:

X	P	Year	Type	Y		X	P	Year	Type	Y
X	1234	2022	O	Y		X	1234	2022	F	Y
X	1234	2022	F	Y		X	1234	2023	F	Y
X	1234	2023	O	Y		X	1234	2021	O	Y
X	1234	2023	F	Y		X	12345	2023	O	Y
X	1234	2021	O	Y		X	12345	2022	O	Y
X	12345	2023	O	Y		X	12345	2021	A	Y
X	12345	2022	O	Y
X	12345	2021	O	Y
X	12345	2021	F	Y
X	12345	2021	A	Y

Basically, I need to filter based on a hierarchy key on column Year and Type. For example in this, A > F > O. E.g. for 1234 in 2022 since both O and F exist, we just want F. However, in 12345 2023, only O exists so we will take O. X and Y are misc columns whose data should be kept. At the very end, only one row for each year should exist. 

Any help is appreciated. 

[apathetichell]

create numeric values for f/o/a - wherein f is either the largest or smallest number.

use summarize tool in group by X/P/Year/Y  - take the max of your numeric equivalent colum of Type (or minimum if F is the minimum). Join it back on the numeric equivalent/X/P/Year/Y