Combining two types of recordsin obiee

Question

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!

3153063 · Answer

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

3153063 · Answer

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

Daniel Willis · Answer

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.