Skip to Content
author's profile photo Former Member
Former Member

How to Group by in Analytic View

Hi All,

We have an analytic view that joins several tables in the data foundation (measures only from 1 table as allowed). We have a requirement where there is a many to 1 relationship via a join from 1 table to another. We only want 1 record from the many table and want to do this by having MAX function by a particular date. So even though there are many records via the join, only show 1 record. Is there any way to do this at the analytic level? We could write an SQL calculation view, but having a group by should be possible without having to write massive SQL for a calculation view. Is this possible? Thanks!

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jun 11, 2012 at 02:21 PM

    Hi Mike,

    Not sure if I understood you correctly, but if you just want to always only bring back records from the many table in your analytical view based on the maximum date, you could have a SQL satement as follows:

    SELECT COL_A FROM "_SYS_BIC".<analytical_view> WHERE DATE_COLUMN IN (SELECT MAX(DATE_COLUMN) FROM "_SYS_BIC".<analytical_view>) GROUP BY COL_A;

    Thanks,

    Anooj

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Mike,

      Can you please provide some sample data and expected result. I think the max aggregation in analytic view with the join functionality in the calc view should be helpful.

      But it is difficult to make the comment without knowing the exact requirement. Hence asking for the sample data.

      Regards,

      Ravi

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.