3 Replies Latest reply on Dec 7, 2018 5:22 AM by Jim Dehner

    Multi-Column Count

    Christine Erle

      Hi all,

       

      we just took over a new responsibility in our company and are struggling a bit with the structure of the new data.I hope you guys can help

       

      The data we look at is structured as follows:
      Columns: ID, Item 1, Item 2, Item 3, ..

       

      The content in the item columns can be similar, meaning that items we see in 'Item 1' can also appear in 'Item 2' and so on.
      Also per ID we can have the same item multiple times.

       

      Ultimate goal is to count the appearance per item.

      A simple example:

      Result should be:

       

      Basically we need to pivot our data but can't in Tableau as it is a SQL data source.

       

      One solution we came up with for now is to union our data source with itself as often as we have different items. But as the data source is quite large already it now takes a lot of time for refreshes, calculations etc.

       

      Any suggestions are greatly welcome.

       

      Thanks,
      Chrisi

        • 1. Re: Multi-Column Count
          Jim Dehner

          Good morning - yes that is ugly -

          Is this a one time change that you will use to correct the data structure then move on - if so you could use Tableau Prep to clean and pivot the file

           

          Good luck'

          Jim

          • 2. Re: Multi-Column Count
            Christine Erle

            Thanks for your quick reply, Jim.

             

            Unfortunately we do not have Tableau Prep available.

             

            We need the change to then move on with our analysis - so it is a long-term thing.
            That's why we were rather looking for some calculated fields to imitate a pivot in order to reduce the run times.

             

            Cheers,

            Chrisi

            • 3. Re: Multi-Column Count
              Jim Dehner

              Hi again

               

              see the attached  - i'm not certain this is any easier but it is worth a look

               

              first I created a scaffold (see the excel sheet) - there is a sheet for Products , one of ID's and a third for you matrix

              then created 3 calculated fields one for each "Item"

              then an lod to total

               

              it returns this

               

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              2 of 2 people found this helpful