Event Date field generates too multiple rows for the same job req phase OTBI

Question

Hi experts,

we have created an OTBI analysis with the following fields taken from the 2 subject areas: Recruiting real time and Recruiting event real time.

We have seen that the Event Date column generates too many rows for the same  event phase code. Is it possible to remove all those multiple rows?  thanks in advance. Regards.

GraemeHarper · Answer

Hi Oriana L.,

I encountered the same issue in OTBI whilst trying to include both Recruiting Real Time and Recruiting Events Real Time subject areas.

I cannot understand the root cause of why this is happening, but can offer a workaround.

You can use the RCOUNT function to strip out the duplicates.

If you create a new column with this SQL:

RCOUNT("Job Application History - Event Details"."Event Date" BY "Candidate"."Candidate Number","Job Requisition - Basic Information"."Requisition Number","Job Application History - Event Details"."Event Phase Code") it will assign a count to each like below (although please note I have used Event Phase ID not Code in the screenshot):

You can then filter this column to = 1 in the filters and hide the column, and you will return only one row per Requisition Number, Candidate and Event.

I hope this helps.

Thanks,

Graeme