Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OAC Dataflow Formula

Accepted answer
53
Views
7
Comments
Miguel Cardoso
Miguel Cardoso Rank 1 - Community Starter
in Oracle Analytics Cloud and Server

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.

Screenshot 2024-06-20 102652.png

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!

Best Answers

  • Federico Venturin - Oracle
    Federico Venturin - Oracle Mod
    Answer ✓

    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
    Gianni Ceresa Mod
    Answer ✓

    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.

Answers

  • Federico Venturin - Oracle
    Federico Venturin - Oracle Mod

    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
    Gianni Ceresa Mod

    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
    Mostafa Morsy-Oracle Rank 6 - Analytics Lead

    @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
    Miguel Cardoso Rank 1 - Community Starter

    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.

  • Miguel Cardoso
    Miguel Cardoso Rank 1 - Community Starter

    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