Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 216 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
BI Answers: Filtering data in all columns
Hello all,
I try to set up a filter which present data in every part of dimension. For Example:
I want to see every customer who bought something in every location of my Company:
My Company contains 3 locations: A, B and C
Customer - Location - Value
Customer A - Location A - $100.000
Customer A - Location B - $50.000
Customer A - Location C - $10.000
Customer B - Location A - $100.000
Customer B - Location B - $10.000
Customer C - Location C - $100.000
In this example, I want to filter only Customer A, because B did not purchase anything in location C and C did not purchase anything in locations A and B.
I am able to proceed with Count function, but this solution is completely uneffective as I am facing thousands of locations and customers, so I try to set up a filter on data base, but for now I have no idea how should it looks.
Can anyone fix this one?
Answers
-
Agreed a count is ineffective ... and a filter on the database reduces the table to only serving up the combinations where a customer has purchases from all three locations ... really what you want?
Maybe think about the setup of a materialized view that is refreshed everyday with the customers that have a positive value in ALL locations-- model that as it's own fact table. A fact star answers a specific set of questions ...
The view could be something similar to:
SELECT Customer,
Location,
Value
FROM tbl
WHERE Customer IN
(SELECT Customer
FROM tbl
WHERE Location IN ('A','B','C')
GROUP BY Customer
HAVING SUM(
CASE
WHEN Location = 'A'
THEN 1
ELSE 0
END) > 0
AND SUM(
CASE
WHEN Location = 'B'
THEN 1
ELSE 0
END) > 0
AND SUM(
CASE
WHEN Location = 'C'
THEN 1
ELSE 0
END) > 0
)
0 -
Ok, thank you Thomas for your answer.
However, I got million rows error so was unable to use it.
Finally I used union and filter it by Case function.
It is uneffective, but works.
0