Forum Discussion
Return the highest value from another table for each row
I have a table of projects (one row per project), then a table for risks and another for issues associated to each project (there can be any number of rows per project in these tables). I'd like to add two columns to the projects table that returns a number of 1-3 forming an overall risk and issue rating for the project, based on the ratings specified in the risks and issues tables.
Each risk and issue added to a project is given a rating of 1-3: 1=Green, 2=Yellow, 3=Red. If a red risk or issue exists on a project, I want the overall project risk/issue rating to be 3. Then if a yellow risk or issue exists and a red one doesn't, the overall rating should be 2. If no yellow or red ones exist, it should be 1.
Is it possible to perform a merge between these tables but have only the highest number returned? Any other ideas on the simplest way to achieve this would be appreciated.
12 Replies
Hi Anonymous,
If there is relationship between the three tables? Could you please create three fake sample table table for further analysis? So that we can post solution which is close to your requirement. Thanks for understanding.
Best Regards,
AngeliaHi Angelia
Yes there is a relationship between the 3 tables via the ProjectId. These are the standard tables for Project Online available via the Project Reporting OData feed (I've just renamed a lot of the columns). Attached is a screenshot showing the relationship between the tables and sample of the risks/issues tables that shows the Priority value; which is a Whole Number (1-3). I was hoping to perform a type of merge that picks up the first Priority value for each Project Id; similar to how a VLOOKUP would work in Excel where it returns the first match it finds (if I sorted the Risks/Issues tables descending by Priority, it would pick up the highest one for each project).
Thanks
Olivia
Hi Anonymous,
Share the link from where i can download your file and also show the expected result.
