1 2 Previous Next 19 Replies Latest reply on Sep 5, 2017 6:15 AM by Leanne Vermeulen Go to original post
      • 15. Re: Customers that have NOT ordered a product?
        Leanne Vermeulen

        Okay managed to get hold of a PC for this - Simon Runc could you provide me with the SQL code necessary if I wanted to compare two dimensions per Customer? So instead of just Products per Customer, I'd need both Product and Product Category by Customer.

         

        Obviously the first table you created above would need to be able to produce all combinations, but only where applicable. (I've tried working out the code, but can't seem to come right). So (hypothetically), if the Product contained Carrots and Raspberries and Product Category contained Fruit and Vegetables, I'd need all possible but only relevant combinations. For example:

         

        Customer01 - Carrots - Vegetables

        Customer01 - Raspberries - Fruit

         

        and NOT

         

        Customer01 - Carrots - Vegetables

        Customer01 - Carrots - Fruit

        Customer01 - Raspberries - Vegetables

        Customer01 - Raspberries - Fruit

         

        I'm pretty certain you'll already have figured that part out and I'm not even sure that would even be a possible issue, but I just thought I'd be extra safe and mention it anyway

        • 16. Re: Customers that have NOT ordered a product?
          Simon Runc

          hi Leanne,

           

          Yes I forgot CustomSQL isn't available for Macs, and while you could do this with VBA...it would likely, depending on number of products/customer, take a long time to run!! It terms of other options...I'd suggest using a database (mySQL is a free on I like, or MS SQL has a free version for Schema's under 10GBs), you can install an instance locally and will be much more flexible (and fast) for handling re-shaping (customSQL) and larger datasets. However as you have got your hands on a PC....

           

          As adding the Category doesn't increase the Level of Detail of the data (it's just an attribute of Product) we can change the first custom SQL to

           

          SELECT [c].[Customer Name] AS [Customer Name],

            [p].[Product Name] AS [Product Name],

            [p].[Category] AS [Category]

          FROM (

            SELECT [Orders$].[Product Name] AS [Product Name],

            [Orders$].[Category] AS [Category],

            1 AS [Dummy]

            FROM [Orders$]

            GROUP BY [Orders$].[Product Name],

              [Orders$].[Category]

          ) [p]

            INNER JOIN (

            SELECT [Orders$].[Customer Name] AS [Customer Name],

            1 AS [Dummy]

            FROM [Orders$]

            GROUP BY [Orders$].[Customer Name],

              [Orders$].[Category]

          ) [c] ON [p].[Dummy] = [c].[Dummy]

          GROUP BY [c].[Customer Name],

            [p].[Product Name],

            [p].[Category]

           

          hope that does the trick, but let me know if not

          • 17. Re: Customers that have NOT ordered a product?
            Leanne Vermeulen

            Thanks - the new code works perfectly. Only issue is the second bit of code for the aggregate - I keep getting this message:

            "Database error 0x80040E07: Data type mismatch in criteria expression."

             

            Which, from what I've read may have something to do with one of the fields being a different value type? I looked up troubleshooting for the issue - Excel file is definitely 'up to date' / upgraded and all data in that partucular column is a value type, so I doubt those are the issues.

            • 18. Re: Customers that have NOT ordered a product?
              Simon Runc

              hi Leanne,

               

              Hmmm...it does work fine on my superstore version.

               

              SELECT [Orders$].[Customer Name] AS [Customer Name],

              [Orders$].[Product Name] AS [Product Name],

              SUM([Orders$].[Sales]) AS [Sales]

              FROM [Orders$]

              GROUP BY [Orders$].[Customer Name],

              [Orders$].[Product Name]

               

              Does it let you run the second piece of SQL? or does the error occur when you try and left join it to the first part?

               

              The only field here I can think of would be the Sale field. Is it definitely a value? it may look like a value, but Excel might have formatted it as text... when you just bring this Sheet, raw, into Tableau what is the data type of sales?

               

              • 19. Re: Customers that have NOT ordered a product?
                Leanne Vermeulen

                Hmm you were right - I had previously converted the data source from CSV to Excel to get it to work with legacy connection and it seems that may be when that field was concerted to string. All works now! Thank you

                1 2 Previous Next