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

OBIEE Analysis With Union Returns Duplicate Rows

Received Response
266
Views
4
Comments
ca$hmon3y
ca$hmon3y Rank 3 - Community Apprentice
edited Aug 13, 2024 5:16PM in Oracle Analytics Cloud and Server

For reference, we are using 11.1.1.9.0.

I am trying to create an analysis combining two subject areas with a union. Columns A and B come from Subject Area 1, and columns B, C, and D come from Subject Area 2. The subject areas have only column B in common. When I try to do a union, the report creates separate rows for the columns that do not align (ie. there is a row for data within columns A and B, and then another row for data with columns B, C, and D.) I created dummy columns to join the subject areas, but am having no success. Any suggestions would be highly appreciated.

Update: I believe the issue is similar to that described here (https://community.oracle.com/thread/3567836), but I cannot find a workaround for the issue.

Answers

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    That is the expected behavior for that kind of union.  You can combine the record pairs into single lines either by using a pivot table that uses column B as the row definition, or use Result Columns that are calculated outside the union using the position of the columns. (saw_0, saw_1, etc.)

  • ca$hmon3y
    ca$hmon3y Rank 3 - Community Apprentice

    Hi Jerry, thanks for your quick response. I added a Result Column and inputted columns using saw_0, saw_1, etc. However, I am seeing the same result as before, where the records are disjointed across two lines. Am I supposed to do any sort of calculation on the column?

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    Yes, because you are trying to combine two records into one.  Since column B (saw_2) is the shared column, each result column must will look like

    max(saw_1 by saw_2) or sum(saw_1 by saw_2).  Keep in mind there will will still be two records for each Col B, so you'll need to work with pivot tables for presentations.

  • ca$hmon3y
    ca$hmon3y Rank 3 - Community Apprentice

    Jerry, that worked. Thanks so much for your help - I learned something new today in OBIEE!