I'm using Business Objects 4.3 to query my HR system and have 4 objects on the report from one table with multiple records. I want the latest record and the corresponding values for each object.
Example:
| Person ID | Check Date | Renewal Date | Reference Number |
|---|---|---|---|
| 4566 | 1/1/23 | 31/12/23 | 123 |
| 4566 | 1/1/24 | 31/12/24 | 456 |
All columns currently show MULTIVALUE against the person ID (key field) as the check data is from a sub query. I have used Max on the Check Date to get the latest entry but I don't want to put max on the other columns in case of errors in the data. EG if I put max on both date columns I would get the bold entries instead of a renewal date of 31/12/23
| Person ID | Check Date | Renewal Date | Reference Number |
|---|---|---|---|
| 4566 | 1/1/23 | 31/12/24 | 123 |
| 4566 | 1/1/24 | 31/12/23 | 456 |
So how can I make the variable for Renewal Date or Reference Number dependent on the entry returned for Check Date, ie. return the whole corresponding record? I tried where check date = max(check date) but it didn't like that.
Thanks
