Return Most Recent Record For Each Unique ID

Jason Farlette shared this question 6 years ago
Answered

I have a view which looks like:


Date ID Value

01-MAY-13 12345 100

01-MAY-13 13345 120

03-MAY-13 12345 140

03-MAY-13 14345 140

05-MAY-13 12345 150

05-MAY-13 13345 160


I want to generate a report containing only the most recent record for each ID.


The SQL would be:

select * from TestView a where Date = (select max(Date) from TestView b where b.ID = a.ID);


The returned result would be:

Date ID Value

03-MAY-13 14345 140

05-MAY-13 13345 160

05-MAY-13 12345 150


I would have thought that I should be able to use an Intersection subquery to generate this report with the:

- two queries linked by ID

- subquery filtering on the max Date

- main query filter linked to the subquery filter


However that does not work. It always results in an error retrieving the results.


In Yellowfin it looks like:

89529fb0b4df41fbe23d59fd269f5bc3

4d24a6fa45c241b49e1224085efedff0

705a1eed767c54748a7c97e6c335797e


Any suggestion on what I am doing wrong appreciated.

Best Answer
photo

Hi Jason,

Thanks for reaching out with your question. Generally, working with or reporting on specific data sets or scenarios is going to fall under Consulting and isn't generally covered by the Yellowfin Support Contract.

That being said, in this particular scenario I did find a valid solution to display your results as desired.

In my example, I'm using Ski Team data. My report has been designed to show the most recent record for each unique Athlete ID and show the age metric of that record.

28221b1643824e1738f262a53f2f602c

I've sorted the data shown Ascending by Athlete ID to ensure it's only returning one record per ID.

To do this, I used and Append subquery set up as follows:

fedd6e1565813b82dc81542b5fb8382c

I found that applying a Max Date Filter was limiting my data to the Maximum date in the entire result set. To better serve the scenario, I chose the 'Maximum' aggregation against the date field. This allowed YF to display the records as desired.

Does this help?

Thanks,

Ryan

Replies (3)

photo
1

Hi Jason,

Thanks for reaching out with your question. Generally, working with or reporting on specific data sets or scenarios is going to fall under Consulting and isn't generally covered by the Yellowfin Support Contract.

That being said, in this particular scenario I did find a valid solution to display your results as desired.

In my example, I'm using Ski Team data. My report has been designed to show the most recent record for each unique Athlete ID and show the age metric of that record.

28221b1643824e1738f262a53f2f602c

I've sorted the data shown Ascending by Athlete ID to ensure it's only returning one record per ID.

To do this, I used and Append subquery set up as follows:

fedd6e1565813b82dc81542b5fb8382c

I found that applying a Max Date Filter was limiting my data to the Maximum date in the entire result set. To better serve the scenario, I chose the 'Maximum' aggregation against the date field. This allowed YF to display the records as desired.

Does this help?

Thanks,

Ryan

photo
1

For some reason it works more smoothly with your data set but as long as I work through the display bugs with mine I eventually get the right result.

Thanks for your help.

photo
1

Hi Jason,

Thanks for the update. I'm glad this helped you come to a solution with your report. I'll go ahead and mark this as Answered. Please don't hesitate to reach out with further questions or issues.

Thanks,

Ryan

Leave a Comment
 
Attach a file