Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
249
Views
4
Comments
User_Y73CE
User_Y73CE Rank 1 - Community Starter
edited Aug 13, 2024 5:12PM in Oracle Analytics Cloud and Server

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:


Tagged:

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    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 Rank 1 - Community Starter

    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 Mod

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


  • User_Y73CE
    User_Y73CE Rank 1 - Community Starter

    Thank you Gianni - you made my day ☺️