1 2 Previous Next 19 Replies Latest reply on Apr 1, 2016 9:17 AM by Yuriy Fal

    List of Paired products

    Santanu Mukherjee

      Hi,

      I have a basket of transactions for customers

           

      customer idproduct
      12Bread
      12Pizza
      12Pretzel
      34Pizza
      34Smoothie
      45Bread
      45Pizza
      45Smoothie
      45Candy
      67Candy
      89Bread
      89Pizza
      89Pretzel
      89Smoothie
      89Candy
      89Energy Drink
      89Soda

       

      I want to know the number of customers who bought paired products like

       

          

      Group Number of Customers Who Bought Both
      Bread & Pizza                    3
      Bread & Pretzel                  2
      Bread & Smoothie              2
      Bread & Energy Drink         1
      Bread & Candy                   2

      ...etc.

       

       

      I want tableau to spit out this information without the user having to do anything once I select the data source and the data is available to Tableau.

        • 1. Re: List of Paired products
          Yuriy Fal

          Hi Santanu,

           

          Something like this?

          Please find the attached wb (version 9.0).

           

          Hope it could help.

           

          Yours,

          Yuri

          • 2. Re: List of Paired products
            Santanu Mukherjee

            Thanks Yuiry,

             

            Can you please explain me the steps that you did so that it is easier for me to understand?

             

             

            Thanks,

             

            Santanu Mukherjee | Manager-Data Analytics

            Koupon Media

            C. 410.790.4104

            @KouponMedia

             

            1. Relentless Execution

             

            • 3. Re: List of Paired products
              Mosin K

              Two steps are required.

              #1 Select your fields on interest and create a Combined field.

              combinedfield.png

              #2 Select the combined field and change the separator to " & "

              editcombinedfield.png

              • 4. Re: List of Paired products
                Yuriy Fal

                OK.

                 

                1) First, the Basket.

                 

                In your case each [customer id] is a Basket.

                When making a datasource one could do

                a self-join of your source table (to it's copy)

                using [customer id] as a join field.

                 

                The resulting dataset consists of all combinations

                of original rows (products) for each [customer id].

                Each row in the result set contains a pair of [product] fields.

                 

                2) Second, the Items in the Basket.

                 

                In your case [product] is an Item.

                So one could filter product pairs further

                to leave only one pair combo (from two possible ones) --

                and filter out rows in which product is paired onto itself, too.

                 

                This could be done with a row-level filter such as

                [product 1] < [product 2]

                 

                Hope this makes sense.

                 

                Yours,

                Yuri

                • 5. Re: List of Paired products
                  Santanu Mukherjee

                  Hi,

                   

                  Sorry for the delay in responding.

                  I want to know how you created the table Basket$ and Basket$1?

                   

                   

                   

                   

                   

                  Thanks,

                   

                  Santanu Mukherjee | Manager-Data Analytics

                  Koupon Media

                  C. 410.790.4104

                  @KouponMedia

                   

                  1. Relentless Execution

                   

                  • 6. Re: List of Paired products
                    Yuriy Fal

                    Hi Santanu,

                     

                    I just copied your data into an Excel table

                    and connect Tableau to it, then do a self-join.

                     

                    Please take a look at the Data Source tab

                    in my workbook (above in the thread).

                     

                    Hope it helps.

                     

                    Yours,

                    Yuri

                     

                    Please find the attached an Excel file with your data.

                    • 7. Re: List of Paired products
                      Santanu Mukherjee

                      Hi Yuri,

                       

                      I understood the process of self-join of the same model data source and then doing the paired products. But I have a bigger question / scenario

                       

                       

                       

                      Hi,

                       

                      The scenario is as under:

                       

                      1. I connect Tableau to a MYSQL database as the data source

                       

                      2. I run a query and that query gives me half a million rows with 2 or 3 columns.

                       

                      3. I want to use the above half a million rows as my new data source.

                       

                      How do I do it?

                       

                       

                       

                       

                       

                       

                      Thanks,

                       

                      Santanu Mukherjee | Manager-Data Analytics

                      Koupon Media

                      C. 410.790.4104

                      @KouponMedia

                       

                      1. Relentless Execution

                       

                      • 8. Re: List of Paired products
                        Yuriy Fal

                        Hi Santanu,

                         

                        The same approach is valid -- even if your datasource is large.

                         

                        You could do self-join your query with two join conditions:

                         

                        1) INNER JOIN by your "Basket" field ( query1.customer_id = query2.customer_id ).

                        2) INNER JOIN by your "Item" field ( query1.product_id < query2.product_id ) -- this should be a non-equi join.

                         

                        For performance reasons it could be better

                        to materialize your query (create table as select ...)

                        and index both customer_id and product_id fields.

                         

                        Moreover, creating Tableau extract is recommended.

                         

                        Yours,

                        Yuri

                        • 9. Re: List of Paired products
                          Santanu Mukherjee

                          Thanks Yuri, BUT

                           

                          My data source is large and it does not contain the column product.

                          I have written a huge query by adding strings together (name, type, description) and based on keywords defined the product.

                          Once I run the query I will get  a result set of 2 columns – customer and product and the magnitude is millions of records.

                          Now I need to make this result set a data source and do inner join.

                           

                          How do I do that ?

                           

                           

                           

                           

                           

                          Thanks,

                           

                          Santanu Mukherjee | Manager-Data Analytics

                          Koupon Media

                          C. 410.790.4104

                          @KouponMedia

                           

                          1. Relentless Execution

                           

                          • 10. Re: List of Paired products
                            Santanu Mukherjee

                            Hi,

                             

                            I did not understand the entire process. Are you suggesting?

                            1. 1. Run the query on the database.
                            2. 2. Once I get the queried results I will have to export to .csv and then use this .csv as extract?
                            3. 3. If that is TRUE or not, I would have to create the extract every day as would be static for a point in time.

                             

                            Am I missing something?

                            • 11. Re: List of Paired products
                              Yuriy Fal

                              Hi Santanu,

                               

                              Sorry for a delay, here is my understanding:

                               

                              1. Run the query on the database.

                              -- This is an obvious first step.

                               

                              2. Once I get the queried results I will have to export to .csv and then use this .csv as extract?

                              -- Yes, but exporting to .csv is a last resort. Tableau doesn't allow a non-equi-join on Text / Excel files.

                              So you would be doing an inner-join on your Customer field only, then use a filter after creating an extract.

                              Just the same as done in my sample workbook.

                               

                              You'd better to join your query result set from the Step 1 to itself

                              right in your DBMS of choice (MySQL?), then do an extract from this.

                              This approach has been described in my previous message above.

                              Doing this you'd be able to use a non-equi-join (ON query1.product1 < query2.product2).

                               

                              3. If that is TRUE or not, I would have to create the extract every day as would be static for a point in time.

                              -- Yes, it is TRUE.

                               

                               

                              Yours,

                              Yuri

                              • 12. Re: List of Paired products
                                Jonathan Drummey

                                Hi Yuriy,

                                 

                                re: #2: Technically Tableau can do a non-equi-join for text and Excel files

                                using the legacy connector and then Custom SQL. It's painful, but possible.

                                 

                                Jonathan

                                 

                                 

                                 

                                 

                                On Sun, Mar 27, 2016 at 1:36 PM, Yuriy Fal <tableaucommunity@tableau.com>

                                • 13. Re: List of Paired products
                                  Yuriy Fal

                                  Hi Jonathan,

                                   

                                  Agree, though this could be

                                  even more problematic

                                  taking into account

                                  millions of rows in a dataset

                                  as mentioned above.

                                   

                                  Yours,

                                  Yuri

                                  • 14. Re: List of Paired products
                                    Jonathan Drummey

                                    I’d missed that part…I have had cross products that have generated millions of records in JET SQL so it’s doable, but anything involving millions of records and Excel/Access/JET SQL is going to be painfully slow.

                                     

                                    Jonathan

                                    1 2 Previous Next