How to Group by in Analytic View

[Former Member]

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!

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Hi Anooj,

Yes, we are aware that we can write the SQL (we are very familiar with SQL), the question is, where to write this?  Do we have to write the entire sql in the calculation view?  We believe so.  The example you have above is referencing an analytic view.  So we are assuming we setup our analytic view with this many relationship.  Then write a calcation view (via manual sql) and reference the analytic view and perform the group by there?  Can you confirm this is what you meant?

We were hoping there was some type of function or something at the analytic level where we could integrate this.

Thanks much!

[Former Member · Accepted solution]

Hi Mike,

That depends on where you woud want to use your Analytical view - for e.g if you are consuming your analytical view via a Business Objects universe (and then a BOBJ reporting tool on top of that), you could write the SQL within the Universe. If you want to fetch the analytical view result in a stored procedue, you could write it within there or If you wanted this result to be unioned/joined with another analytical view, then yeah you could write it in a calculation view.

Thanks,

Anooj

[former_member184768 · Accepted solution]

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