3 Replies Latest reply on Jul 20, 2016 10:03 AM by Nicholas Hara

    Left Join diplays incorrect values

    Jason Kleinhans

      Good day

      I have edited my current sql script by doing a left join to another table which contains filters in its data , here is the script I have entered , my main table is the value entry table. Tableau directly connects to my relational datasource and not my cubes

       

       

      LEFT JOIN [dbo].[Transfer Excise Tbl]

       

      ON (([Transfer Excise Tbl].[No_] = [Spier Live$Value Entry].[Item No_])

      AND ([Spier Live$Value Entry].[Location Code] = [Transfer Excise Tbl].[Location Code])

      AND (Datediff(y,[Spier Live$Value Entry].[Posting Date],[Transfer Excise Tbl].[Starting Date]) > -365)

      AND (Datediff(y,[Spier Live$Value Entry].[Posting Date],[Transfer Excise Tbl].[Starting Date]) < 0))

      WHERE (([Spier Live$Value Entry].[Posting Date] > '2013-02-26')

      AND ([Spier Live$Value Entry].[Gen_ Bus_ Posting Group] IN ('LOCA','EXSA'))

      AND ([Spier Live$Value Entry].[Invoiced Quantity] <> 0))

       

      I also use an OLAP Cube report so that I can compare the values vs Tableau as I go along developing to make sure the values are 100%
      . In my OLAP cube report my sales actual value was 32,145,105.66 and now with the added on Query the value changed to

       

      I have attached my workbook with the edited SQL Query which displays the incorrect sales actual value

       

      if you need any further info please let me know

       

       

       

       

        • 1. Re: Left Join diplays incorrect values
          Jason Kleinhans

          Does anyone have an idea of the issue i posted , either a yes or no answer because i have seen many views but no one replied

          • 2. Re: Left Join diplays incorrect values
            Dmitry Chirkov

            Well.. thing here that it's pretty impossible to tell what's going in on here.

             

            For starters, you are comparing data from two different sources and there's always a chance of data mismatch there.

            Second, you are comparing relational data with cube data - are you familiar with cube's configuration as to how it aggregates its data?

            And last, you wrote your SQL query (joins, grouping, filters) by hand so there's not not a lot of room for Tableau to screw anything up.

             

            My advice is to unroll this quite complex workbook and start with basics, i.e. is SUM([Sales Amount (Actual)]) correct (227,390,962)? If not - then SQL query needs to be fixed.

             

            And there's blending as well!

             

            p.s. this is definitely not a topic for newbie section - you are a pro!

            • 3. Re: Left Join diplays incorrect values
              Nicholas Hara

              Jason,

               

              I agree with Dmitry. Right now, it is really difficult to dissect where the issue might be occurring.

               

              Try this:

              1. Run your custom sql query in a third party SQL tool and see what the Sum([Sales Amount(Actual)] is.

              2. If the answer is the same as the one Tableau has, then it is either something wrong with the query OR it is something wrong with the underlying data that your Cube isn't catching

              3. If the answer is NOT the same as the one Tableau has, then there is probably something going on with the driver or the way Tableau is writing the query - Open a support case

               

              As a side note, it looks like your Custom SQL query can be handled by adding data source filters and using a traditional connection in Tableau. I'm not a SQL expert by any means, but maybe you can elucidate why using the data pane does not work for your needs?