2 Replies Latest reply on Feb 25, 2016 5:13 PM by Chester Abulencia

    How to display duplicate rows?

    Chester Abulencia

      Hi,

       

      My problem is, tableau showing 2 rows with the same values from excel as one row. However in this case, i need to show that 2 rows so that my count could match from the data source. I have tried dragging the dimensions associated to that row to make it up to the lowest level of detail and more specific as possible. But still it doesn't show up as 2 rows since the values are identical. Screenshot is for your reference (only a dummy data).

       

      This is the source.

       

       

      This is the view on tableau (As you can see Data 1 is shown as 1 row, which is the opposite of what i need. I need to show 2 rows of Data 1)

       

      Thank you so much!

        • 1. Re: How to display duplicate rows?
          Jonathan Drummey

          Hi Chester,

           

          Here's a solution that involves a table calculation and changing Tableau's default behavior:

           

          Starting out with your original view, I set Analysis->Aggregate Measures->Off. This turns off mark aggregation so Tableau now displays a mark for every single record and we see two Abc's for Data1/a/b:

           

           

          Then I created the Rank calculation. This has the formula RANK_UNIQUE(SUM([Number of Records])) and an Advanced... Compute Using on F1, F2, and F3:

           

          That is placed as a discrete (blue pill) to the left and tells Tableau to use an alphanumeric sort.

           

          The reason why we need to do this is that Tableau and Excel are fundamentally different in how they approach data & representing data. Excel approaches data as a set of cells. Rows and columns in Excel are visual distinctions, they have no intrinsic meaning. The layout that we see in Excel is WYSIWYG. In Tableau the default behavior is to aggregate the underlying records (rows) based on the dimensions (columns) in the view. The data set as-is does not have sufficient dimensionality (granularity) to uniquely identify each unique record that you want to display. To use common Tableau terminology this is a a data set that is "too tall" for what you want to do because it doesn't have enough granularity.

           

          The workaround described here turns Tableau's default aggregation behavior to enable each underlying record to be displayed and then adds the table calculation to iterate over the records to create an index that can generate the desired display. In Excel data sources that I control I avoid this problem by adding =ROW() as a column in the data, in databases I'll use an autogenerated number, GUID, etc. to ensure uniqueness when the data lacks the needed dimensionality. Row Number  is a feature request that would help address this sort of problem, please vote it up if that is something that you'd like to see in Tableau.

           

          v9.2 workbook is attached.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: How to display duplicate rows?
            Chester Abulencia

            Hi Jonathan,

             

            Thank you so much! It worked! First, I thought that there is an option to turn off automatic aggregation of data. Which in this case really has. Hahaha! Again, thank you so much! This is an additional knowledge to me .