Combining two types of recordsin obiee — Oracle Analytics

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

Combining two types of recordsin obiee

Received Response
21
Views
6
Comments
3153063
3153063 Rank 2 - Community Beginner
edited Aug 13, 2024 7:09PM in Oracle Analytics Cloud and Server

I am new and still learning what can be done in obiee. I am trying to combine two types of records and eliminate ones that don't have full info. For example,

111, beginning time, 0100

111, ending time, 0500

222, beginning time, 0200

333, beginning time, 0300

333, ending time, 0500

Desired result is full info (beginning and ending time) based on an identifying number. Assume there will always be a beginning time.

111  beginning time 0100

ending time 0500

333 beginning time 0300

ending time 0500

(222 is eliminated due to lack of ending time).

Any help is appreciated. Thanks!

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    First of all a question: Do you really have that information in two different rows in the database and not just in two attribute columns after the ID?

    Like  this:

    ID   | Start           | End                |

    ---------------------------------------------------

    111 | 01.01.2016  | 03.01.2016      |

    222 | 04.01.2016 | 09.01.2016      |

    As for what you call "combine" that's the Column Property "Value Suppression" which should be activated by default:

    pastedImage_0.png

    As for "eliminate" it'd be super easy if the data was stored as I said above (just a filter "End IS NULL") so I'll wait for your update.

  • 3153063
    3153063 Rank 2 - Community Beginner

    Christian,

    Thank you for the quick reply. Unfortunately, it is in 2 different rows. (There's more info in each record, but I was simplifying it for the question).

    Yes, the Suppress is working. I just would like it smarter. :-)

    Thanks!

    Ron

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Smarter as in automatically suppressing your 222?

    Because you will need to do that with selection steps. A filter won't help you there.

  • Daniel Willis
    Daniel Willis Rank 4 - Community Specialist

    How about thinking about this a bit differently

    Assuming beginning time will always be less than end time and there will only ever be a beginning time and optionally an end time.

    Assuming column names are "ID", "TIMETYPE" and "TIME"

    Can you

    1. select ID, Min(Time BY ID) AS "Start Time", Max(Time BY ID) AS "End Time"

    2. and have a filter "MAX(TIMETYPE BY ID) is equal to / is in  ending time" (to filter out the IDs with no end time)

    That should put everything on 1 row which is not exactly what you asked for initially but maybe even better? Think it should all work although I haven't tested above.

  • 3153063
    3153063 Rank 2 - Community Beginner

    Daniel,

    Thanks for the reply. I prefer not to key off the time elements. However, (along the same line of thinking I hope) is there a way to determine how many times the record number (111,222,333) appears and then filter out if less than 2?

    Thanks,

    Ron

  • 3153063
    3153063 Rank 2 - Community Beginner

    Okay, I figured out how to do it.

    I created the column:

    COUNT("TIME_TYPE" BY "RECORD_NUM")

    and then filtered on more than 1 hit.

    Thanks to Gerardnico for explaining the BY clause.

    OBIEE - Aggregation rules in Logical SQL with the GROUP BY and BY clause [Gerardnico]

    Ron