Oracle Communities

Oracle Analytics and AI

Child Item

OAC Dataflow Formula

Hi! I'm hoping someone can shed some light on this (apologies if this is the wrong forum!).

I'm trying to do something very simple with 1 dataset, but it doesn't seem to be working. My dataset has 2 columns I need to check - Person Number and Manager Person Number - and what I'm trying to achieve is to check if the Person Number exists within the Manager Person Number column, then I want the formula to return 'Y'. For this, I've written:

CASE WHEN PERSON NUMBER IN (MANAGER PERSON NUMBER) THEN 'Y' ELSE 'N' END

OAC validates this successfully, but it returns 'N' for everyone.

I really don't understand what I'm doing wrong here, could anyone share some ideas?

P.S: I have found a workaround, but it involves an additional dataflow, which I would prefer to avoid.

Many thanks!

Find more posts tagged with

Accepted answers

Federico Venturin - Oracle

Hi @Miguel Cardoso ,

I'm sorry! I was thinking that the Manager Person Number contains multiple values per row.

You don't need to upload the dataset twice and create a second data flow. You can add the original dataset twice to your existing data flow, select the column you are interested in, and then join them directly in the same data flow.

Gianni Ceresa

Needing 2 datasets is what I believe is your only solution as of now, because you need to have the managers and the employees as "separate" lists to be able to check for existence of one in the other list.

Also, because it isn't about counting things, there aren't really simple workarounds by trying to "aggregate" at a higher level: you aren't aggregating, you are just checking existence.

You could maybe avoid uploading the Excel spreadsheet a second time and just using a dataflow doing the whole job (using the same dataset twice and doing some manipulations inside the dataflow itself). Then your dataflow save the result in a new dataset that will contain the "Line Manager?" column directly and will be the result of the manipulations done by the dataflow.

All comments

Federico Venturin - Oracle

Hi @Miguel Cardoso ,

I'd use the following expression:

CASE WHEN LOCATE(Person Number, Manage Person Number) > 0 THEN 'Y' ELSE 'N' END

LOCATE returns the numerical position of a character string (Person Number) in another character string (Manage Person Number). When it returns 0, it means that the string has not been found.

Gianni Ceresa

Hi @Miguel Cardoso ,

The expression you posted, and the one Federico provided, will be evaluated only at the row level. It will return "Y" only if a person is self-managed.

Are you trying to look if "Person Number" exists anywhere as a "Manager Person Number" in your whole dataset to identify by Y the persons being managers?

Expressions are evaluated at each and every row, and it isn't as simple as in Excel to have the formula searching for a person number in the whole "column".

Mostafa Morsy-Oracle

@Miguel Cardoso

I am thinking if your Dataset source is DB to use a DB Function to achieve this requirements and then use it in your formula

Miguel Cardoso

Hi all,

Many thanks for your replies :)

Federico Venturin - I tried your suggestion, but all rows are returning 'N', which as Gianni Ceresa mentioned is due to the formula working at a row level instead of column.

Mostafa Morsy-Oracle - The dataset in question is an Excel spreadsheet exported from another system and uploaded to OAC. Whilst we could do this in Excel before uploading, we're trying to avoid any manual intervention.

At the moment, the workaround I've found is to upload the dataset twice (under different names) and create a second Dataflow selecting only the 'Manager Person Number' column, and then joining that Output with the main Dataflow.

Federico Venturin - Oracle

Hi @Miguel Cardoso ,

I'm sorry! I was thinking that the Manager Person Number contains multiple values per row.

Gianni Ceresa

Also, because it isn't about counting things, there aren't really simple workarounds by trying to "aggregate" at a higher level: you aren't aggregating, you are just checking existence.

Miguel Cardoso

Federico Venturin and Gianni Ceresa - Thank you :) I've tried this and it's working! Unsure why I felt the need to create a second Dataflow to begin with, but your suggestion is much easier and efficient