Sony Hejmady Posted August 12, 2019 Share Posted August 12, 2019 I have a scnario and here is my dataset below: Reviewer is my calculated column where I would needData Reviewer id_DB is null thenData Reviewer id_file. I have various dates in which I need to pick max date considering Compco, Account by grouping.Hence I need to get 333 and 777 which derives from latest dates. My current query is : case when [Data Reviewer id_DB] is null then [Data Reviewer id_file] else [Data Reviewer id_DB] over Max([Report Date]) end Please help me in getting the expected values as shown below for Reviewer. Date Compco Account Data Reviewer id_DB Data Reviewer id_file Reviewer 8/10/2019 3026 1100501001 111 444 8/11/2019 3026 1100501001 212 345 8/12/2019 3026 1100501001 333 666 333 8/10/2019 3026 2200501002 454 567 8/11/2019 3026 2200501002 432 578 8/12/2019 3026 2200501002 777 888 777 Link to comment Share on other sites More sharing options...
Richard Pobi Posted August 12, 2019 Share Posted August 12, 2019 I was able to achieve the result-set by creating two calculated columns. Calculated columnA DenseRank([Date],"desc",[Account],[Compco]) Calculated columnB case when [Calculated columnA]=1 then [Data Reviewer id_DB] END Link to comment Share on other sites More sharing options...
Sony Hejmady Posted August 13, 2019 Author Share Posted August 13, 2019 Thank you so much.It worked. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now