12 Replies Latest reply on Mar 17, 2014 11:01 PM by james.diaz

    filter duplicate rows

    Jeff Stanley

      Falling into the beyond my control column, I have data where a number of the records are duplicated for reasons that are helpful in some analysis, but creating problems in my current table. I have sales data where for instance 3 salespeople may share an account, unfortunately the revenue number for each salesperson contains the full amount of revenue, rather than 1/3'rd so the total revenue number for this group is tripled. A single sale will have 3 records, with the correct commission(1/3 of the total commission) for each sales person, but the entire revenue number is listed in each record. 

       

       

      The duplicate records all share a common "tag_no," what would be the method to only include a single instance for each tag_no?

        • 1. Re: filter duplicate rows
          Tom W

          What is the data source you're connecting to?

           

          Is it possible for you to change context to retrieve different values?

           

          I.e. if you're doing a commission report, can you query a view which contains the duplicated / triplicated revenue data by sales person? And similarly, if you need to run a company revenue report, do you have a view which only contains the 1 sales record?

          • 2. Re: filter duplicate rows
            Jeff Stanley

            unfortunately I cannot change the context, the only solution I can see is to use tableau to filter out the duplicate tag_no's since the only number I am after in the record is the revenue number. I guess a better way to put it is to say I need the revenue number per unique tag_no

            • 3. Re: filter duplicate rows
              Tom W

              You could use the MIN / MAX instead of SUM if you are looking at the data at the invoice / row level. I assume you're probably going to be looking at a higher level than this, like total company, in which case this won't work.

               

              Can you attach a packaged workbook?

               

              You could achieve this with Custom SQL by applying the DISTINCT clause.

              • 4. Re: Re: filter duplicate rows
                Jeff Stanley

                I have attached a sample, looking at the data below:

                 

                tab.JPG.jpg

                 

                each recond has a unique Tag_no, except the last three which share the same number. I am trying to include all the unique tag_no, and a single representative of any that are duplicated - so as the table sits now tag _no 29 equates to 450 in commission (150 * 3), while I need it to only equate to 150

                • 5. Re: Re: filter duplicate rows
                  Tom W

                  I assume Excel is your data source then based on your example.

                   

                  I would use custom sql. Right click the data source, Edit Connection and select custom sql.

                  It should generate a select statement based on the columns.

                   

                  I would look to do something like this:

                  - SELECT DISTINCT (will select only the distinct records)

                  - Remove the account column (if you have three different accounts for the same tag no, these would all be 'distinct' and you'll still have the duplicates)

                   

                  SELECT DISTINCT

                    [Sheet1$].[Commisssion] AS [Commisssion],

                    [Sheet1$].[F1] AS [F1],

                    [Sheet1$].[Last Trade color] AS [Last Trade color],

                    [Sheet1$].[Number of Records] AS [Number of Records],

                    [Sheet1$].[Tag_no] AS [Tag_no],

                    [Sheet1$].[Trade Date] AS [Trade Date]

                  FROM [Sheet1$]

                  • 6. Re: filter duplicate rows
                    Jeff Stanley

                    excel is the data source for the sample, sql is the data source for the actual tableau tables. This particular table is part of a larger workbook and I cannot modify the dataset. In other words I am stuck trying to come up with some way to ignore the duplicate records in tableau

                    • 7. Re: filter duplicate rows
                      Tom W

                      I'm not sure you're going to be able to do it.

                       

                      As I said earlier, if you're looking at the row level data, you can run a MIN or MAX with the TagNo on the rows shelf and you'll get the number you want. Using the same approach you could create a calculated field which is Commission / count(tag_no), but this will only work when you're at the TagNo level. You want to roll up to account and the context of the calculation changes and it will be wrong.

                       

                      Given that you are using two different contexts here (1: Account level Commission, 2: Company Revenue) I think the only way you get to what you want is by having two connections in your workbook. 1 like it is now, the second with the distinct clause.

                      • 8. Re: filter duplicate rows
                        Jeff Stanley

                        not sure I am explaining this correctly, looking at the table above - I am wanting to total the commissions for the accounts in the account names column, ignoring any of the rows that contain a duplicate tag_no

                        • 9. Re: filter duplicate rows
                          Tom W

                          I still think you're going to need to use a DISTINCT select, just including the account as well.

                          • 10. Re: filter duplicate rows
                            Jeff Stanley

                            coming at it from a different direction - could I somehow add up all the par amounts for a tag_no and then divide by the count for that tag_no

                            • 11. Re: filter duplicate rows
                              Tom W

                              See post 7 of mine above
                              "Using the same approach you could create a calculated field which is Commission / count(tag_no), but this will only work when you're at the TagNo level. You want to roll up to account and the context of the calculation changes and it will be wrong."

                               

                              If you made a report with this calculation and the TagNo, it will calculate correctly.

                              If you remove the TagNo from the report and try view it at Account level, it will calculate the total of the commission / the count of all tags numbers. It's effectively going to calculate it at the level Account level which is wrong. Hence why you can't use this.

                              • 12. Re: filter duplicate rows
                                james.diaz

                                if you have a field denoting row number or similar, this thread may be helpful for what youre trying to do: Incremental count of strings in a column