1 2 Previous Next 20 Replies Latest reply on Feb 9, 2018 7:19 AM by Yuriy Fal

    Emulating a Subquery

    D Anderson

      Hello, I am fairly new to Tableau and I have a problem that I think (hope) is relatively simple.

       

      I have created a very simple example of the problem below and included this simple dataset in a packaged workbook for your convenience.

      I am using Tableau version 10.4

       

      I have a products table as follows

      ProductsCustomer Ref
      BreadC
      CheeseA
      CheeseB
      ChocC
      ChocD
      FishA
      MeatE
      MilkB
      MilkC
      MilkD

       

      From this I want to be able to filter on a product and show what other products those customers also purchased.

       

      For example using the table above if I filtered on Milk I would expect

       

      ProductsCount
      Bread1
      Cheese1
      Choc2

       

      I would need to select multiple products so if I filtered on Milk and Cheese (asking the question what other products do people who purchase milk and cheese buy)

      I would expect 

      ProductsCount
      Bread1
      Choc2
      Fish1

       

      I am really stuck as how to do this in Tableau. I would solve this problem in SQL using a subquery along the lines of

       

      Declare @Product varchar(10)

      SET @Product = 'Milk'

        SELECT products,count(customer_ref) as counts

          FROM   #prodTable

          WHERE products NOT IN(@Product) AND

      customer_ref IN

                        (SELECT prod_sub.customer_ref AS prod_sub_customer_ref

                         FROM  #prodTable AS prod_sub

                         WHERE  products IN ( @Product) 

                         GROUP  BY prod_sub.customer_ref)

      GROUP  BY products

       

      Output:

       

      productscounts
      Bread     1
      Cheese    1
      Choc      2

       

      In Tableau I originally thought I could solve this using a parameter but I can't seem to get round the problem of needing to filter on multiple products.

      I had the most success creating two identical datasets joining them and doing a filter on one but I struggled to exclude the original filtered products.

       

      Many thanks for looking at this - I am sure I have missed something simple and look forward to your responses

        • 1. Re: Emulating a Subquery
          Norbert Maijoor

          Hi D.

           

          Find my approach based on Market Basket Analyses as reference below and stored in attached workbook version 10.4 located in the original thread

           

           

          The approach is explained here

          2 of 2 people found this helpful
          • 2. Re: Emulating a Subquery
            D Anderson

            Thank you for the taking the time to have a look at this, it is much appreciated.

             

            I have added a filter to your workbook to do some testing - It seems to work fine when selecting one product to filter

             

            Filtering on Milk returns Bread 1, Cheese 1 and Choc 2 as expected

             

            However filtering on Milk AND Cheese should return Bread 1, Choc 2 and Fish 1 but instead it returns Bread 1, Cheese 1, Choc 2, Fish 1, Milk 1.

             

            This is the problem I had when joining the two datasets together - sorry if I didn't make that clear

             

            I have attached your version of the workbook with the filter added.

             

            Thanks again for looking at this

             

            Regards

            • 3. Re: Emulating a Subquery
              Norbert Maijoor

              Hi D.

               

              Please correct me if i am "wrong" . But "Phonetically" you are trying to give an answer concerning a total different question.

               

              Initially the question was. If a customer puts product A in the basket what other products did the cutomer also put in the basket?

               

              Your latest question is: If a customer puts product A & B in the basket what other products did the customer also put in the  basket?

               

              Please confirm

               

              Regards,

              Norbert

              • 4. Re: Emulating a Subquery
                D Anderson

                Norbert,

                 

                Thanks again for looking at this, it is much appreciated.

                 

                Sorry If it was not clear i thought I covered the problem with multiple products in the line below but realise it could be ambiguous

                 

                I would need to select multiple products so if I filtered on Milk and Cheese (asking the question what other products do people who purchase milk and cheese buy)

                 

                So if they filtered on one product it would show all other products people who bought this got, If they select two or more products it again would show just other products that people who bought any of these got not members of the original selection - the sql above would do this if the set statement was changed to

                Declare @Product (

                   Value varchar(1000)

                )

                 

                insert into @Product values ('Milk')

                insert into @Product values ('Cheese')    

                 

                Thanks again for your help with this

                 

                D

                 

                 

                 

                 

                 

                • 5. Re: Emulating a Subquery
                  Norbert Maijoor

                  Hi D.

                   

                  I am puzzled I am not sure if we can proceed on the existing dataset/structure and would like to ask my fellow ambassador Simon Runc to the table.

                  Simon Runc could you "shine a little light" on this one.

                   

                  Upfront thanks a TON

                   

                  Regards,

                  Norbert

                  • 6. Re: Emulating a Subquery
                    Simon Runc

                    hi Norbert,

                     

                    Cross Shopping...right in my line of work!!

                     

                    So this one is tricky in Tableau, especially as you want to select multiple items, and you want to select these on a dropdown. For just one product, we can use parameters and sets, with the existing data, but for multiple products we need to do a little something to the data!

                     

                    I would add, in defense of Tableau!, that we could achieve this multi-product functionality with the exisitng data set, if we "hard code" the products to explore into the formula (very much as you have to do with the @ declarations in SQL

                     

                    So to get this functionalitu we need to Join the data with itself, on the Customer Reference

                     

                     

                    This creates extra rows in the data...but we do now have this multi-select functionality.

                     

                    Hope that helps.

                    2 of 2 people found this helpful
                    • 7. Re: Emulating a Subquery
                      Norbert Maijoor

                      Hi Simon,

                       

                      I was in the "wrong" corner;)...to  much focused on the product and totally forgot we have the customer ref in the dataset... Of course...thanks for the "assist"

                       

                      Regards,

                      Norbert

                      • 8. Re: Emulating a Subquery
                        D Anderson

                        Many thanks Simon for looking at this.

                         

                        This was how I original joined the dataset with itself, However the problem I was having was finding a way to get the results to exclude the original products. Any ideas would be much appreciated

                         

                          

                        Filter on Milk and Cheese
                        Show what other products those Customers that bought milk or cheese also purchased
                        Result
                        ProductsCount
                        Bread1
                        Choc2
                        Fish1
                        • 9. Re: Emulating a Subquery
                          Simon Runc

                          So this is a tricky one!...and not sure it's going to be possible (out the box) in Tableau.

                           

                          We can add a filter, to not include the selected product(s) like this

                          [Filter out Selected Product]

                          [Products] != [Products Also Purchased]

                           

                          but the problem comes in when we have multiple products selected...

                           

                          If we look at Customer B, who bought both Cheese and Milk...we can filter out (from the Product != Product Also Purchases) their Milk (Also purchased) from the Milk Product (and likewise for Cheese), but in their Milk row, this won't filter out that they also bought cheese! (similarly with Milk in the Cheese Product Row)

                           

                          We have some ability to move up and down "rows", using Table Calculations, but here we need a looping check so it can check if cheese (product) also exists  (in Also purchased) in any other Row (even a Milk product row)...and then so the same for Cheese (and any other selections)

                           

                          I'll have a think (...I hate to admit defeat!) and also ping Jonathan Drummey ...who might be able to think of a way.

                          • 10. Re: Emulating a Subquery
                            Ruchika R

                            Hello Anderson,

                             

                            How about this approach -

                             

                            You Click on the Products and it filters the relevant values in the More Info Sheet.

                             

                             

                            More Info WorkSheet

                             

                             

                            Source Worksheet

                             

                             

                            Apply Action -

                             

                             

                            Regards,

                            Ruchika

                            4 of 4 people found this helpful
                            • 11. Re: Emulating a Subquery
                              Simon Runc

                              Nice work Ruchika

                               

                              This is a way we can achieve the results we want, without messing with the data.

                               

                              We can also here do the "don't show selected products" filter working too (with a little trick!)

                               

                              So first I set up the action sending the Customer Reference(s) to the "Also Bought" sheet (as per your solution). I then set up this action

                               

                               

                              I trigger it, so I can find this action filter in the "Also Bought" sheet, and edit it to be a "Exclude" filter

                               

                               

                              Which I learned from Rody's and Jonathan's recent blogs...on Filter Action Parameters!

                               

                              I hadn't thought of doing it this way, so thanks for sharing (...always a good day, when you learn something new!)

                              3 of 3 people found this helpful
                              • 12. Re: Emulating a Subquery
                                Jonathan Drummey

                                In the attached workbook are four solutions:

                                 

                                1) My preferred solution if the user experience is ok: two worksheets on a dashboard with a filter action that keeps customers and a second filter action that removes the selected products. Upon writing this post I saw that Ruchika R and Simon Runc had done the same thing.

                                 

                                2) A solution using the self-joined data source (that is effectively a cross product) using an INCLUDE LOD expression to deal with the multiple filter selection issue that Simon had pinged me about by aggregating across each Customer/Products also Purchased to identify whether any products were selected and only counting customers that didn't have any of those selected products. This would generally be my second choice so long as the self-joined data source wasn't too large.

                                 

                                3) A solution using the self-joined data source a FIXED LOD expression. I'd built this prior to the INCLUDE LOD expression because I find that FIXED LODs are generally easier to build & validate than INCLUDE LODs, however this solution requires using context filters for the Product and other filters and that can impact performance plus it's extra work for maintaining views.

                                 

                                4) A data blend solution. Instead of using a join we use a self-data blend. This is perhaps the most complicated because in order to do the detection of multiple filter selection the view needs to have the Customer dimension in it and then we need table calculations to 'back out' of that complexity. Plus the Products filter always has a Null value in it that is added by Tableau that we can't get rid of.

                                 

                                This is a good example of how there are often multiple ways in Tableau to get to the same result! v10.4 workbook is attached.

                                 

                                Jonathan

                                3 of 3 people found this helpful
                                • 13. Re: Emulating a Subquery
                                  Simon Runc

                                  That's great Jonathan...4 for the price of 1!!

                                   

                                  Really like the INCLUDE version...I got as far as realizing I had to COUNT stuff in the "Also Purchased" and compare it to the selection, but couldn't quite get my head what!

                                   

                                  Many thanks for helping out (...an even better day, when you learn 2 new things!!)

                                  • 14. Re: Emulating a Subquery
                                    Gerardo Varela

                                    Mr. Anderson,

                                         First off, I don't have solution for you. The gents above (and now below) already covered that.   I would like to welcome you to the wonderful Tableau community. For your first post, this is a fantastic representation of a what in my mind is a perfect question!  You stated your level of confidence in Tableau, the version your using,  you provided the data, the expected output, showed how you would do it in something you are comfortable in, and most importantly a packaged workbook.  Fantastic first post!

                                     

                                    EDIT: Made to include Yuriy's solution

                                    Regards,

                                    Gerardo

                                    1 2 Previous Next