Categories
- All Categories
- 89 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14.2K Oracle Analytics Forums
- 5.3K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 53 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations Gallery
- 2 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
The OBIEE Equivalent of Countif or FIlter on Duplicates
Answers
-
“COUNT(DISTINCT "Position"."Position Description" By "Employee Attributes"."Employee Number", "Workforce Profile Event Fact Details"."Last Hire Date")”
Unfortunately, the above doesn’t work. It returns inconsistent results as well. It looks like it’s closer. The numbers returned in the columns are only 1, 2 or 3. This returns about 1300 > 1. However when I look at the results in Excel using a countif of the concatenated(column it only returns 400. Maybe Robert is correct about the aggregation issue.... but if this is true it looks to be happening before aggregation not after??? RIght?
COUNT(CONCAT("Employee Attributes"."Employee Number", CAST("Workforce Profile Event Fact Details"."Last Hire Date” AS CHAR)))
I thought the above may work but I get some kind of strange error:
0 -
Hello,
Martins first answer will work
COUNT("Fact"."Employee ID" By "Fact"."Employee ID" , "Fact"."Hire Date" ) > 1
add a column for the above formula and keep a filter for the same as >1 and delete the column from criteria
0 -
Use the audit insert date to the table ... if you don't have one there's your issue - it's a deficient physical model in the database. Easy then to rank on the order of insert dates and filter for rank != 1 ...
This also gives you the benefit of:
- last row in (filter on a rank=1 where rank is over insert date ordered descending)
- first row in (filter on a rank=1 where rank is over insert date ordered ascending)
- average number of re-inserts (average of rank column)
0 -
Asim,
Unfortunately this does not work:
“COUNT(DISTINCT "Position"."Position Description" By "Employee Attributes"."Employee Number", "Workforce Profile Event Fact Details"."Last Hire Date")”>1
Unfortunately, the above doesn’t work. It returns inconsistent results as well. It looks like it’s closer. The numbers returned in the columns are only 1, 2 or 3. This returns about 1300 > 1. However when I look at the results in Excel using a countif of the concatenated(column it only returns 400. Maybe Robert is correct about the aggregation issue.... but if this is true it looks to be happening before aggregation not after??? RIght?
Not sure why but I can definitely confirm that there are no EE ID & Last Hire Date combinations that occur 8 or 11 times in the subset of data. I think the Count logic is being applied before my criteria filters which makes the results inaccurate.
0 -
Please provide create statement(tables) and insert script(for few sample records), so i can check it in my environment.
0 -
Hi again,
see => https://gerardnico.com/wiki/dat/obiee/obis/measure_nested_aggregate
In particular -> Complex Nested Aggregate : Aggregate of a conditional aggregate
Thoughts; -
Do you have any access to the ETL to add a new measure to achieve what you need?
Have you tried including ALL columns involved and adding an additional filter column to do count of values when match up to all of the columns present apart from the column having the duplicates; sure there was a better way to express that! - my reason for ALL columns is that this will guarantee the integrity of what is returned and prevent any pre-client aggregation that could throw your result out.
Whenever I see case and sum logic in OBIEE my heart sinks and if I can then I push the logic back to the repository or the ETL, I have seen this work and then after a save and reload not work, when everything should be the same.
0 -
Thanks for the tip. When I use Date in the query it eliminates the duplicates. How simple! I hope I haven't wasted to much of your time!
THANK YOU EVERYONE, PROBLEM SOLVED!!!
0 -
Robert & Asim,
I don't have access to the ETL and can't provide the script to create tables.... However, by simply adding in the Date Dimension it fixed the duplicate issue.
Thank you for taking the time to review my inquiry. I appreciate the assistance.
0