1 2 3 4 Previous Next 54 Replies Latest reply on Mar 4, 2016 1:12 PM by ann.stolzman

    Dynamic Columns in Tableau ?

    Santanu Mukherjee

      have another problem regarding tableau on which I need help.

      The use case is:

      I have multiple customers say C1 – C100 and each customer has bought multiple products. One customer can buy 10 products, some can buy 5 , some can buy 3 etc…

       

      I have a transactional database (mysql) where there is one single row for each transaction by a customer and each transaction has only one product.

       

      So what I need in tableau is - (the number of customers or the category of products cannot be fixed or hardcoded – because it is based on transaction)

       

      StartFragment

      Customer id

      Bread

      Butter

      Jam

      Pizza

      Pretzel

      c1

      x

      x

      x

      x

       

       

       

       

      c2

      x

      x

       

       

       

       

       

       

      c3

      x

       

      x

      x

      x

       

       

       

      c4

       

      x

       

      x

      x

       

       

       

      c5

      x

      x

      x

      x

      x

      x

      x

       

      c6

       

       

      x

      x

       

      x

      x

       

       

       

       

       

       

       

       

       

      ..

       

       

       

       

       

       

       

       

      ..

       

       

       

       

       

       

       

       

      ..

       

       

       

        • 1. Re: Dynamic Columns in Tableau ?
          ann.stolzman

          Hi Santanu,

          Do you have some sample data, or can you mock some data up, in a packaged workbook that we can work with?

           

          -Ann-

          2 of 2 people found this helpful
          • 2. Re: Dynamic Columns in Tableau ?
            Santanu Mukherjee

            Here is my sample data from the analytics table

            customer id     product

            12      Bread

            12      Pizza

            12      Pretzel

            34      Pizza

            34      Smoothie

            45      Bread

            45      Pizza

            45      Smoothie

            45      Candy

            67      Candy

            89      Bread

            89      Pizza

            89      Pretzel

            89      Smoothie

            89      Candy

            89      Energy Drink

            89      Soda

             

             

            Here is what I want tableau will show me

             

            Customer id     Bread   Pizza   Pretzel Smoothie        Candy   Energy Drink    Soda

            12      x       x       x

            34              x               x

            45      x       x               x       x

            67                                      x

            89      x       x       x       x       x       x       x

             

             

             

             

            Thanks,

             

            Santanu Mukherjee | Manager-Data Analytics

            Koupon Media

            C. 410.790.4104

            @KouponMedia

             

            1. Relentless Execution

             

            • 3. Re: Dynamic Columns in Tableau ?
              ann.stolzman

              Hi Santanu,

               

              Ok, this is an easy one, unless I'm missing something.  I've created a workbook in Tableau 9.2 (see attached).

               

              Just drag your Customer ID to rows, Product to columns, and count of customer id is your measure.  If you don't want a number, and just an X, then change the mark to Shape instead of automatic.  You'll get a shape and a count, just drag the count to the shape button that appears in the marks shelf after you changed to Shape from Automatic, and adjust from there.

               

              -A-

              1 of 1 people found this helpful
              • 4. Re: Dynamic Columns in Tableau ?
                Santanu Mukherjee

                Sorry I am not able to view it as it says I need to upgrade Tableau

                 

                The other point is that the sample data that I gave is the result set from a SQL query. How Do I fit that into the equation?

                 

                 

                Thanks,

                 

                Santanu Mukherjee | Manager-Data Analytics

                Koupon Media

                C. 410.790.4104

                @KouponMedia

                 

                1. Relentless Execution

                 

                • 5. Re: Dynamic Columns in Tableau ?
                  ann.stolzman

                  What version of Tableau are you using?  I could recreate it in 9.1 if that's helpful, although I think the walkthrough I have there is pretty clear.

                   

                  Where you are pulling the data from doesn't really matter.  If you've got it in a CSV file, then you just connect to the CSV file in Tableau.  If you're connected to a live data source, the query pulls the data in, and you've still got the field names.  Tableau is more of a "dumb interface" that allows you to connect to and work with the data - it's only as good as what you put into it.  

                   

                  -Ann-

                  • 6. Re: Dynamic Columns in Tableau ?
                    Santanu Mukherjee

                    Thanks Ann, my version of tableau is 9.0.2. Can you please recreate it in this version?

                     

                     

                    Thanks,

                     

                    Santanu Mukherjee | Manager-Data Analytics

                    Koupon Media

                    C. 410.790.4104

                    @KouponMedia

                     

                    1. Relentless Execution

                     

                    • 7. Re: Dynamic Columns in Tableau ?
                      ann.stolzman

                      Santanu,

                       

                      I had to redownload and reinstall 9.0 to do this - were my instructions in an earlier post of how to do this not clear?  I just want to be sure I explained things clearly.

                       

                      That said, here's a version created in 9.0.  I hope this helps.

                       

                      -Ann-

                      • 8. Re: Dynamic Columns in Tableau ?
                        Santanu Mukherjee

                        Thanks Ann. I have updated to version 9.2. I will have to find a way to get the SQL that produces the products to integrate with this in Tableau. But probably this is the first step.

                        My end goal is to find out how many customers bought each pair,

                        For example, in this sample, I want to find out how many customers bought bread and pizza, pizza and pretzel and so on. Knowing that the number of transactions are dynamic and can increase every moment,

                        How do I find that out?

                         

                         

                         

                         

                         

                        Thanks,

                         

                        Santanu Mukherjee | Manager-Data Analytics

                        Koupon Media

                        C. 410.790.4104

                        @KouponMedia

                         

                        1. Relentless Execution

                         

                        • 9. Re: Dynamic Columns in Tableau ?
                          ann.stolzman

                          Santanu,

                          The way the data is laid out in Tableau right now, you are getting who bought what.  If there were multiple records for each customer buying multiple things, then we could show how many times the customer bought the same thing (by using Count).

                           

                          Do you want to count the total customers, or the distinct customers?   Look at the first workbook that I posted - that shows the Distinct Count of Customers.  Remove the Customer ID from the rows shelf, and right click and change the formula to Count if you want the total count of customers.  That will give you the count of customers (or you can change to distinct count, if you don't care how many times it was purchased, but how many unique purchasers there were).

                           

                          Make sense?

                           

                          As for the SQL, you can connect to your live database, and when you get connected and see the list of table names available to use, you can select New Custom SQL at the bottom to write your custom statement that brings the data in.

                           

                          -Ann-

                          • 10. Re: Dynamic Columns in Tableau ?
                            Chris McClellan

                            You've got a few choices here, the easiest one would be to put the SQL code in a view and then use the view in Tableau.

                             

                            You could also copy the SQL code into Tableau (Custom SQL query), the result will be the same but I would recommend leaving the SQL code in MySQL.

                            • 11. Re: Dynamic Columns in Tableau ?
                              Santanu Mukherjee

                              Hi,

                              I am not able to do that in SQL. Did you get the question?

                               

                              The question was

                              But what I want is

                              Total customers who bought (bread and Pizza) – in this example it will be 3 (customer id 12,45 and 89)

                              Total customers who bought pizza and pretzel -  in this example it will be 2 (customer id 12 and 89)

                              Total customers who bought pizza and smoothie – in this example it will be 3 (customer id 34,45 and 89)

                              And so on….

                              So total number of customers for each pair.

                               

                               

                              Thanks,

                               

                              Santanu Mukherjee | Manager-Data Analytics

                              Koupon Media

                              C. 410.790.4104

                              @KouponMedia

                               

                              1. Relentless Execution

                               

                              • 12. Re: Dynamic Columns in Tableau ?
                                Chris McClellan

                                Sorry, I was replying to :

                                 

                                The other point is that the sample data that I gave is the result set from a SQL query. How Do I fit that into the equation?

                                 

                                 

                                So I thought you were meaning : How do I get my SQL query into Tableau.

                                 

                                You probably want to create some sets (in reply to your current question).

                                • 13. Re: Dynamic Columns in Tableau ?
                                  ann.stolzman

                                  Hi Chris,

                                  If Santanu is trying to match each and every single item with every other single item (so each item is grouped one at a time with everything else), creating sets might not be the right way.  He'd be creating 42 sets, and would have to create more for new products when they're introduced.

                                   

                                  I'm wondering if some trickery with filtering or parameters would work.

                                   

                                  Santanu, are users going to be selecting the items that they want to look up, or are you looking to present that information without user input?

                                   

                                  -A-

                                  • 14. Re: Dynamic Columns in Tableau ?
                                    Santanu Mukherjee

                                    Hi Ann,

                                     

                                    This is transactional data, but no user input, however I do not know whether new products will come in the next transaction or not, but off course my analysis will be on historical data, but base on the number of products, the grouping will be made.

                                    You are right Ann when you said ' match each and every single item with every other single item'

                                     

                                     

                                     

                                     

                                     

                                    Thanks,

                                     

                                    Santanu Mukherjee | Manager-Data Analytics

                                    Koupon Media

                                    C. 410.790.4104

                                    @KouponMedia

                                     

                                    1. Relentless Execution

                                     

                                    1 2 3 4 Previous Next