Extract data with most recent quarter

[Mukesh_s]

Hi, I am having data as below where the Quarter field is in text format. I want to get rows which is having most recent Quarter.

Quarter	Number
2022Q4	5745
2023Q1	3963
2022Q3	3825
2022Q2	2168
2023Q1	2844
2022Q4	4001
2022Q1	1697

I want to get this data

Quarter	Number
2023Q1	3963
2023Q1	2844

Can any one help me in getting this.

[vinieme12 · Accepted solution]

Option 1) LOAD and Reduce

Main:

Load Quarter,Number,keepchar(Quarter,'0123456789') as QtrNum

From SomeSource;

Inner Join(Main)

Load Max(QtrNum) as QtrNum

Resident Main;

Option 2) Load  max quarter first then filter data

Load Max( keepchar(Quarter,'0123456789') ) as QtrNum

From SomeSource;

Load Quarter,Number

From SomeSource

Where Exists(QtrNum,keepchar(Quarter,'0123456789') ) ;

[Mukesh_s]

That worked. Thanks a lot.