Forum Stats

  • 3,826,853 Users
  • 2,260,713 Discussions
  • 7,897,102 Comments

Discussions

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

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:


Best Answer

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,570 Blue Diamond
    Answer ✓

    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).


    Christian Berg-0racleUser_Y73CE

Answers

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,570 Blue Diamond

    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).

  • User_Y73CE
    User_Y73CE Member Posts: 3 Green Ribbon

    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

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,570 Blue Diamond
    Answer ✓

    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).


    Christian Berg-0racleUser_Y73CE
  • User_Y73CE
    User_Y73CE Member Posts: 3 Green Ribbon

    Thank you Gianni - you made my day ☺️