2 Replies Latest reply on Nov 6, 2018 4:34 AM by Frazer Nash

    Commonality of Sub-Category across Categories

    Frazer Nash

      I have a table of manufactured items in one row and one of its components in the row next to it. I can't attach my data but as an example:

      ProductComponent
      1A
      1B
      1C
      2A
      2D
      2E
      3B
      3C
      3E
      3F

       

      I want to create a table so that you can compare products one and two and see how many components are common. See example below:

      123
      1312
      2131
      3214

       

      So you could use the table to quickly check that products 1 and 3 share 2 components.

      I am using a portion of a dataset at the moment that has 45 products each with around 50 components.

       

      My issue is that if you put the rows and columns both as Product then it will only show how many components for 1 & 1, 2 & 2, and 3 & 3, due to the nature of how Tableau carries that data.

      I decided to test creating a data set with two product columns and one component column, and there was a row for every link. But for 45 products that generated 1.3 million rows, which will not work when I include the rest of the products I'm researching.

      I was hoping there would be a way to format the table in Tableau or use calculated fields/parameters.

       

      Is this achievable in Tableau?

        • 1. Re: Commonality of Sub-Category across Categories
          Zhouyi Zhang

          Hi, Frazer

           

          It is possible, but not sure how big your real data is, but just give it a try

           

          1st, you need join your data to itself as shown below using a key 1=1

           

          2nd, after join, you will have two produce fields, and drag one to column and the other to rows.

          3rd, create a calcuation field to count the common component

           

          A sample workbook attached for your reference as well.

           

          Hope this helps

           

          ZZ

          • 2. Re: Commonality of Sub-Category across Categories
            Frazer Nash

            Hi Zhouyi,

             

            Thank you for your response.

             

            I was hoping to find a different solution as this creates too many data entries. from the 10 rows in the original data set it has increased that to 100 rows.

            With my data set I have many products, I am creating a dashboard with just a sample of 45 products at the moment. But with just 45 products this method generates over 1 million rows, this will not be a viable solution for my full data set. I was hoping for a solution that did not generate any more rows.