Oracle Communities

Oracle Analytics and AI

Child Item

how to put a Countif in a unique field in an OTBI report

Summary:

In my report, I have Person Number field which is unique, however the report has some duplicate entries. I only need to understand how do I get a count to see how many duplicates I get through Person Number field.

What formula do I use to know the count of each Person Number row like we get through Count if in excel.

Content (required):

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

Find more posts tagged with

Reporting

SQL

OTBI

Core HR

FDI

Accepted answers

All comments

Amrita Gupta-Oracle

Hi @Abhishek Bagh ,

You can try below formulae -

MAX(RCOUNT(<"Folder"."Column"> BY <"Folder"."Column">) BY <"Folder"."Column">)

However, the value for this count column will also repeat, as below. I have used the column "Invoice ID" for my test(in place of Person Number) -

MAX(RCOUNT("- General Information"."Invoice ID" BY "- General Information"."Invoice ID") BY "- General Information"."Invoice ID")

Abhishek Bagh

Hi @Amrita Gupta-Oracle - thanks so much for your response, this is really helpful.

However, when i applied this, i do get results like below (which is what you mentioned in your response already). Wondering: is it feasible to get an output for the duplicate records as 1,2,3.. intent is to take them out through the report.

if we could sequence nos. for these duplicates, i will then use a combination to exclude them from my report. Many thanks though for your assistance.

Regards,

Abhishek

Amrita Gupta-Oracle

Use below formulae for the sequence -

RCOUNT("- General Information"."Invoice ID" BY "- General Information"."Invoice ID").

You will get result as below

Please accept the answer if this helps resolve your query.

Abhishek Bagh

Hi @Amrita Gupta-Oracle - I am afraid, because when I use this one, i only see value as 1 for all records (incl. duplicates).. am i missing something in here?

Amrita Gupta-Oracle

Hi Abhishek,

Have you removed the column which was causing Person Number to repeat?

Abhishek Bagh

Hi @Amrita Gupta-Oracle - No I didn't. I feel simple RCount is not providing what you see in the results at your end.

Regards,

Abhishek

Abhishek Bagh

Hi @Amrita Gupta-Oracle - Also, in the meantime, i tried doing some alternatives to do the exclusion.

I put up below formula so that i could exclude 1 & keep only 0 thru filter in Criteria page however when i see the output for below formulae, it only shows 0 for all records (whereas it should show 1 for 4 persons). Not sure what am i missing in here, can you advise pls?

case when MAX (RCOUNT ("Worker"."Person Number" BY "Worker"."Person Number") BY "Worker"."Person Number")>1 AND "Worker"."Termination Date" IS null then 1 else 0 end

Regards,

Abhishek

Amrita Gupta-Oracle

Hi Abhishek,

Could you try applying the same filter using selection steps, instead of doing it using a FILTER. The selection step filters are applied right at the end, so it would do so after the RCOUNT is correctly calculated.

I will try to replicate from my end and let you know.