Oracle Transactional Business Intelligence

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

Compare two fields in analysis

312
Views
14
Comments

Summary

How to compare two fields within an analysis

Content

How do I compare two fields in an analysis.  I need "Workforce Management - Person Real Time"."Person Legislative Information"."Country" equal to "Legal Employer"."Legislation"

Thank you

«1

Comments

  • Michael Scaduto
    Michael Scaduto Rank 3 - Community Apprentice

    Ruth,

    Within an analysis this can be accomplished by a CASE statement, where 'True' is the behavior if there is a match and 'False' if there is not:

    CASE WHEN "Workforce Management - Person Real Time"."Person Legislative Information"."Country" = "Legal Employer"."Legislation" THEN 'True' ELSE 'False' END

    If you are filtering on this criteria you will want to check "Convert this filter to SQL" and then set the SQL filter to "Workforce Management - Person Real Time"."Person Legislative Information"."Country" = "Legal Employer"."Legislation"

  • Ruthie
    Ruthie Rank 3 - Community Apprentice

    I entered the case setting a filter on one of the two columns and using convert to SQL.  I then put in the "Workforce Management - Person Real Time"."Person Legislative Information"."Country" = "Workforce Management - Work Relationship Real Time"."Legal Employer"."Legislation"   When I view the results I receive a huge error. 

    Odbc driver returned an error (SQLExecDirectW).
      Error Details
    Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)
    State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)
    State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000)
    State: HY000. Code: 14023. [nQSError: 14023] None of the fact sources for Dim - Legal Employer.Effective End Date are compatible with the detail filter [Dim - Person Legislative Information.Legislation Code Meaning = Dim - Legal Employer.Legislation]. (HY000)
    State: HY000. Code: 14081. [nQSError: 14081] You may be able to evaluate this query if you remove one of the following column references: Dim - Legal Employer.Effective End Date, Dim - Legal Employer.Effective Start Date, Dim - Legal Employer.OrgUnitClassificationId (HY000)
  • Michael Scaduto
    Michael Scaduto Rank 3 - Community Apprentice

    For testing purposes, does the analysis work if you remove Legal Employer.Effective End Date (or one of the two other fields suggested:  Legal Employer.Effective Start Date or Legal Employer.OrgUnitClassificationId)?  Adding CAST to the filter fields may change the behavior but you'll want to test removing those fields first.

  • Ruthie
    Ruthie Rank 3 - Community Apprentice

    Yes if I remove the filter the analysis works fine.  I'm attaching a screenshot of my filter, perhaps I'm doing something incorrectly.

    Thanks,

    Ruthie

     

  • Shakher Sharma
    Shakher Sharma Rank 4 - Community Specialist

    Please add a screenshot of entire Advanced SQL Filter.

    I tested a similar situation that worked. Please refer to the following screenshot. I guess you are missing "='Ture'" or "='False'" criteria based on your need.

    If this answers your question than please mark this question as answered.

     

    Advanced SQL Filter.png

  • Michael Scaduto
    Michael Scaduto Rank 3 - Community Apprentice

    You should be able to set the fields equal to each other without the case statement using the Advanced SQL Filter

    4-11-2018 10-22-44 AM.jpg

  • Michael Scaduto
    Michael Scaduto Rank 3 - Community Apprentice

    The applied filter would look like this:

    4-11-2018 10-23-06 AM.jpg

  • Ruthie
    Ruthie Rank 3 - Community Apprentice

    CASE WHEN
    "Workforce Management - Person Real Time"."Person Legislative Information"."Country" = "Workforce Management - Worker
    Assignment Real Time"."Legal Employer"."Legislation" THEN 'True' ELSE 'False'
    END 

    I'm also attaching the entire error message I'm receiving.

  • Michael Scaduto
    Michael Scaduto Rank 3 - Community Apprentice

    If the fields are different types this is the cast function I referenced above:

    4-11-2018 10-34-01 AM.jpg

  • Shakher Sharma
    Shakher Sharma Rank 4 - Community Specialist

    Did you built this statement manually? Please try to use Expression Builder to build the same statement. Even converting filter to SQL statement filter is not required. You will be still able to filter the data just like a normal column. 

     

    Can you please go to Advanced tab of analysis and copy & paste text in SQL Issued text box? It will allow me to duplicate your analysis to debug this issue.