How to identify duplicated elements?

A recent Question on the Community asked what would be a good way to identify duplicates. E.g. where an Element has been assigned a Status twice where it only should have one status.

Here is the solution if you are using a SQL Based technology to solve the problem in the table.

Solution:

In your hierarchies panel, right click on Product and choose "Aggregate / Count". Your menu may look different based on the software release you work with, but you should have the "Count" function available in this menu.

Make the Measure shareable so that we can use it in Formulate:

Supporting Table in Discover

Create a Discover view with Product and the new Count Measure. This will tell you how many times each product appears in the table.

There is also a total on the top of the table which shows how many records there are in this table.

Build the formula in Formulate

Now we need to examine the number of times a product has Status A and how many times a product has Status B. In fact we want to know which Products have both Status A and Status B more than once each. In my example I will use "Socialdiscount" which has values of Yes, No and NULL. I'll use Yes and No.

Create another new Measure using Formulate like this:

This is saying, If the count of Product with Status A (SocialDiscount, No) is greater than 1...

...AND the count of Product with Status B (SocialDiscount, Yes) is greater than 1 ,then set the Measure to 1 otherwise 0. When we place this Measure, Flag, on our grid above, those Products that have Status of A (SocialDiscount, No) and B (SocialDiscount, Yes) more than once each will show as 1. We can then filter on Flag to only show those products where my test is true:

So in the data, 157 products, listed, have a Status of A and B more than once each.

If you find this helpful or have questions. Please leave a comment below.