Return Most Recent Record For Each Unique ID
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:
Any suggestion on what I am doing wrong appreciated.
The same question
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.
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:
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
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.
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:
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
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.
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:
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
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.
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:
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
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.
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.
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
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
Replies have been locked on this page!