Oracle Business Intelligence

Products Banner

How to filter to see only rows where the ID has the newest Date?

Received Response
150
Views
4
Comments

I Have a Table as seen in the picture above. I only want to see the rows where the IDs have the newest Value. A seen in the Picture below:


Answers

  • Ideally you solve that in the model or the database level directly by having a view with the "current" values of your rows.

    You could try by adding a rank on date by ID and only keep the rows with 1. Keep in mind the pre & post aggregation filtering issue, so you could look into selection step to keep only the rows you are after (for a post-aggregation filter).

  • Thank you Gianni. Perhaps you can give me another hint? I did at a new column with this Column formula:

    RANK("Date")

    then i get 1,2,3,4 and 5 in the cells. I don't quite get how to insert the "by ID" expression without error:

    RANK('Date') BY 'ID'  does not work and gives Syntax Error

    RANK('Date') FROM 'ID'  does not work and gives Syntax Error

    RANK('Date') FROM ('ID')  does not work and gives Syntax Error

  • You were really close ;-)

    The "BY" goes inside the brackets.

    RANK("table"."column" BY "table"."column") , you should always use the "table"."column" syntax to reference columns in an analysis in OBIEE/OA (assuming that's the product you use as you posted in this category).


  • Thank you Gianni - you made my day ☺️