Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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') ) ;
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') ) ;
That worked. Thanks a lot.