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

JOIN: excluding rows from first query from the results returned by second query

Received Response
11
Views
5
Comments
Wolfrm
Wolfrm Rank 4 - Community Specialist
edited Aug 13, 2024 6:08PM in Oracle Analytics Cloud and Server

The title is probably a bit confusing, but I hope this will be easier to understand:

The dashboard is setup like this:

1. user enters a product_id (single or multiple) and the input is saved in a variable 'user_input'

2. the dashboard then returns all customer_ids associated with each product_id and also lists all other product_ids associated with each customer_id.

I'm using a JOIN: first query returns all the product_ids and some other columns. The second query (UNION) uses user_input to return the customer_ids and and the associated product_ids.

This works as expected, but for one detail. I would like to exclude from the results of each customer_id the one product_id that was entered in the dashboard prompt.

Any ideas or hints how to make this happen would be greatly appreciated.

Answers

  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    Can you provide some sample scenario, so that we will have better clarity.

  • Koduri
    Koduri Rank 1 - Community Starter

    Have a filter report that brings back all the customer id's based on the products (Product Variable) selected. Now use the customer id as the filter (from the Filter Report) for the detail report and also the product variable and put it in not equal to condition on the same report. This should give you what you need. Thank you.

  • Wolfrm
    Wolfrm Rank 4 - Community Specialist

    Yes, certainly:

    User enters into the dashboard prompt two product_ids: product01;product02

    The first query returns product01 and the associated custumer01 etc.

    The second query returns customer01 and all other products they ordered, ie. product11,product22,product33.

    Or like this:

    product_idcustomer_idproduct_id (other products purchased, excluding the product_id from the first column)
    product01customer01product11,product22,product33
    product02customer02product66,product77,product88
  • Wolfrm
    Wolfrm Rank 4 - Community Specialist

    I don't think I can use this quite as you suggest since I have everything in one report, using a UNION JOIN. Filtering like you suggested on the one join did not work.

  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    I would like to exclude from the results of each customer_id the one product_id that was entered in the dashboard prompt.

    ??

    If you have customer and product joined, why cant you simply apply filter on it to get the above result? why need a union?

    just drag and drop customer and product and keep prompt for those columns. when you apply a filter for product it will obviously display all customers associated with the product. Similarly when you apply a filter for customer it will show all products associated with the customer.

    Thanks

    Asim