Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Mukesh_s
Contributor II
Contributor II

Extract data with most recent quarter

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.

Labels (3)
1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

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') ) ;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

2 Replies
vinieme12
Champion III
Champion III

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') ) ;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Mukesh_s
Contributor II
Contributor II
Author

That worked. Thanks a lot.