Oracle Analytics Cloud and Server

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

Syntax to use lookup functions in a single conditional expression in the filter. (HY000) Code: 42047

Received Response
11
Views
2
Comments
Rank 4 - Community Specialist

I am trying to get a single number using the FILTER() Function in Oracle Analytics Answers.  The current expression I have is as follows:

FILTER("Workforce Event Facts"."Department Change Count" USING "Gregorian Calendar"."Year Name" = ValueOf(CURRENT_YEAR) AND "Employment"."User Person Type Name" IN ('Employee', 'Employee_Expat', 'Employee_Expat - Host') AND " Department (Previous) Hierarchy"."Previous LOB Name" = 'Corporate Affairs 11111')

I need to add this additional criteria to the statement so that I can get one returned number:

AND "Department (Previous) Hierarchy"."Previous LOB Name" <> "Department Hierarchy"."LOB Name"

What I want my result to look like this:

pastedImage_4.png

Can some one please tell me how to correct my syntax?  or Create something similar with different logic?

This doesn't seem to work:

FILTER("Workforce Event Facts"."Department Change Count" USING "Gregorian Calendar"."Year Name" = ValueOf(CURRENT_YEAR) AND "Employment"."User Person Type Name" IN ('Employee', 'Employee_Expat', 'Employee_Expat - Host') AND " Department (Previous) Hierarchy"."Previous LOB Name" = 'Corporate Affairs 11111' AND "Department (Previous) Hierarchy"."Previous LOB Name" <> "Department Hierarchy"."LOB Name")

I get an error when I try to do the above.  Any help regarding my issue would be much appreciated.  Thanks in Advance.

Answers

  • Did you try by adding this instead:

    "Department Hierarchy"."LOB Name" <> 'Corporate Affairs 11111' ?

    As you set your "Department (Previous) Hierarchy"."Previous LOB Name" value in the FILTER just hardcode the same value once more

    If you still have an error, would be good to post the error (as it could come from your model but no way to guess without).

  • Rank 4 - Community Specialist

    Thanks so much!!  Not sure how I missed that

Welcome!

It looks like you're new here. Sign in or register to get started.