Oracle Business Intelligence Applications

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Business Intelligence Applications

Categories

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

How to implement NOT EXISTS functionality in OBIEE 12c

Received Response
211
Views
5
Comments
3427576
3427576 Rank 4 - Community Specialist
in Oracle Business Intelligence Applications

Hi Experts,

I am using "CREATE DIRECT DATABASE QUERY" option from OBIEE 12c to meet my requirement.

My requirement : I need to identify the customers who bought product X1 from product X category and not bought product Y1 from product Y category.

The data of Product X1 and product Y1 is available in the same column called Product

The data of Product X category and product Y category is available in the same column called Product Group

Now, I would like to find out how the same requirement (NOT EXISITS) could be met through RPD modelling or OBIEE Analytics.

Please advise!!

Thanks in Advance

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Selection Steps

    https://docs.oracle.com/middleware/12213/biee/BIEUG/GUID-663B5A97-06B1-4486-8C5E-4261B1758581.htm#BIEUG11058

  • 3427576
    3427576 Rank 4 - Community Specialist

    Hi Berg,

    Thanks for your reply. I knew the concept of saving group of objects together and reusing them. I usually do this for saving group of filters. Could you please advise how could I make use of this doc to implement NOT EXISTS functionality or to meet the requirement.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    As I said: selection steps.

    They work with three simple operators which allow you to make any combination conceivable: Keep only, Ad, Remove.

    - start with all customers

    - keep customers who have bought Product 1

    - add customers who have bought Product 2

    With hard filters which actually result in cutting off the data stream in the WHERE clause you can never get that same result.

    Selection steps are applied to the full data stream and do NOT do a hard WHERE product = 1 for example.

  • 3427576
    3427576 Rank 4 - Community Specialist

    Hi Berg,

    Sorry to bother you. I still not able to visualize, how this would serve my need.

    This is the sql query I am using. Please advise

    SELECT CUSTOMERS FROM TXN T1

    WHERE PRODUCT_GROUP IN 'X' AND PRODUCT IN {'X1', 'X2'} AND DOC_DT BETWEEN  TO_DATE ('2018/02/01', 'YYYY/MM/DD')

    AND TO_DATE ('2018/02/28', 'YYYY/MM/DD')

    AND

    NOT EXISTS

    (SELECT CUSTOMERS FROM TXN T2

    WHERE PRODUCT_GROUP IN 'Y' AND PRODUCT IN {'Y1', 'Y2'} AND DOC_DT BETWEEN  TO_DATE ('2018/02/01', 'YYYY/MM/DD')

    AND TO_DATE ('2018/02/28', 'YYYY/MM/DD')

    AND T1.CUSTOMER = T2.CUSTOMER);

  • 3427576
    3427576 Rank 4 - Community Specialist

    Hi,

    In addition,The values for the product and product group are dynamically selected by the user from prompts which are built based on the product master. I am capturing these with in the presentation variables and passing the same in the sql query.

    Please suggest