Forum Stats

  • 3,839,508 Users
  • 2,262,499 Discussions
  • 7,900,982 Comments

Discussions

Categories

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

User_Y73CE
User_Y73CE Member Posts: 3 Green Ribbon
in Business Intelligence Suite Enterprise Edition (OBIEE)
Table.jpg

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:

Table2.jpg


Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,643 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).

    image.png


    Christian Berg-0racleUser_Y73CE
    · Share on TwitterShare on Facebook

Answers

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,643 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).

    · Share on TwitterShare on Facebook
  • 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

    · Share on TwitterShare on Facebook
  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,643 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).

    image.png


    Christian Berg-0racleUser_Y73CE
    · Share on TwitterShare on Facebook
  • User_Y73CE
    User_Y73CE Member Posts: 3 Green Ribbon

    Thank you Gianni - you made my day ☺️

    · Share on TwitterShare on Facebook