7 Replies Latest reply: May 16, 2017 3:27 PM by DALYA ADAMS RSS

    Need to have duplicate results show

    DALYA ADAMS

      I am trying to make a dashboard to track duplicate results in our data. I am currently struggling to get a count and identify these duplicate records.

       

      I have three columns with data similar to below

      IDLocationTerms
      1ANet 0
      1BNet 30
      1BNet 0
      1CNet60
      2BNet 60

       

       

      The way the data is structured is that an ID can have multiple locations underneath it but each location should only be associated with the ID once, ensuring only one set of terms for each location. So what I need to identify is a duplicate Location under an ID. The goal is to pinpoint multiple sets of terms for a Location on an ID record.

       

      In the above example, I'd want to identify the two B locations under ID 1. I wouldn't care about Location B under ID 2. By "under" I am thinking of this in terms of a pivot table (but the solution doesn't have to be in a pivot table).

       

      Thanks in advance for help!!

        • Re: Need to have duplicate results show
          Stefan Wühl

          Try a chart with two dimensions, ID and Location, and an expression like

          =Count(Location)

           

          or

          =Count(DISTINCT Terms)

           

          Where every you see more than '1', there is an issue.

           

          You can also use

          =Count(Location) > 1

          and then filter the results (hide rows with result zero).

            • Re: Need to have duplicate results show
              DALYA ADAMS

              Stefan,

               

              I previously added a Count(Terms) as a measure to my pivot table but the issue is that each ID has lots of terms and that is acceptable. The issue arises when a single location has lots of terms. I have an ID with 25 terms but when I expand on location, each location only has one term associated with it (which is correct). If it has more than 1, it's wrong but I definitely don't want to have to drill down into each of the 60K IDs.

               

              I don't believe I can do count location either because each ID will have lots of locations.

               

              I guess I could do a Count(Location) and a Count(terms) and if the 2 counts don't match there is an issue. Is there a way to limit to only those? Or highlight those IDs?

            • Re: Need to have duplicate results show
              omar bensalem

              A simple table:

              as dimensions: ID, Location

              as measure: count(Location)

               

              As color background:

              if( count(Location)>1,red(),green())

               

              Capture.PNG

                • Re: Need to have duplicate results show
                  DALYA ADAMS

                  Omar,

                   

                  This, in association with my pivot table with ID, Location and Terms, has helped me identify these! Thanks so much!

                  • Re: Need to have duplicate results show
                    DALYA ADAMS

                    Coming across an interesting issue. Some of the data points have duplicate Location but the terms are the same. Is there a way to differentiate this?

                     

                    Example:

                     

                    ID                      Location                       Term

                    3                          B                                Net0

                    3                          B                                Net 0

                    3                          B                                Net 0

                    4                          G                                Net 30

                    4                          G                                Net 60

                    4                          G                                Net 1

                     

                    In the above example ID 4 is more important because of the conflicting information but I'd like to know that there is duplicate information on ID 3.

                     

                    Thanks in advance!