Oracle Analytics and AI

Ameya_J

Hi!

I am working towards building a OTBI candidate diversity report by application stage. In the report, for any applicant who selects more than 1 race ethnicity, i want to recategorize value to "Two or more races"

Trying to use formula proposed by @Raghavendra Raghunath-Oracle in one of the oracle sessions.
CASE WHEN RCOUNT("Job Application - Legislative Information"."Regulatory Response Ethnicity" BY "Candidate Details"."Candidate Number")>1 THEN 'Two or more races' ELSE "Job Application - Legislative Information"."Regulatory Response Ethnicity" END

But instead of single row, I am getting two rows. Please see below example with dummy values. Can someone please help

Nathan CCC

Hi Ameya,

For any aggregate function like sum count min max avg then using BY in function in your select clause will not change how many rows you get. To do that you need will need to aggregate across a "yellow" measure column or include a GROUP BY. The problem here is that the formula is based on 2 "blue" attribute columns in a case rather than function FILTER on a "yellow" measure column with a USING clause. The number of rows you get is determined by the blue attribute columns that you include in your view. We do not know what is the rest of your your query to help. If you post your logical SQL from your advanced tab of your analysis for the "simplest" query to demonstrate the issue I can help more. Do not add an attachment because many readers will be unable to download dur to company security policy regards the risk of downloading files from strangers on internet forums. Just post as text.

Assume this is your test case (old skool "Ask Tom" style)

if this is your data

Candidate, Ethnicity

A, X

A, Y

B, X

then you expect query results with 2 rows

"Candidate", "My Calculation"

"A", "Two or more ethnicity"

"B", "X"

?

Nathan