5 Replies Latest reply on Sep 28, 2012 7:39 AM by Ian Hutchison

    How to blend data accurately

    Ian Hutchison

      I am having problems trying to blend/join to tables from the same datasource.

       

      The both have matching fields within their own tables.

       

      Im looking to merge Budget v actual income for global dimension 2 code (244) for each G_L Account No_ by month.

       

      I have tried creating relationships with very little success.

       

      I have attached a workbook showing the data seperately. Can someone guide me on how to merge this data together into one worksheet?

       

      Many thanks in afvance

       

      Ian

        • 1. Re: How to blend data accurately
          Mark Holtz

          You want your relationships to look like this then:

          relationship.JPG

          Then you just need to make sure ALL fields you join on are in the view where you pull data from the multiple sources.

          (Placing them in the Level of Detail shelf is a 100% guarantee).

          You have GL Account No on the view, but not Global Dimension 2 Code.

           

          I have attached a workbook that pulls both items and then added a calculated field to get the Actual to Budget Variance.

          1 of 1 people found this helpful
          • 2. Re: How to blend data accurately
            Ian Hutchison

            Hi Mark

             

            Thanks for your help on this one. I would however liked to have taken this one stage further and got the budget amounts to be broken down by individual months. I thought I could link the Months from the posting date field within the Navision GL Entry table with the Date field within the GL Budget Entry table.

             

            However this work to some extent but if there is not an entry within the GL Entry table for a particular month then it does not show the budget amount for that month either.

             

            Is there a way to show budget amounts for each month even though there is not an entry within the actuals for that month?

             

            Any help again would be appreciated.

             

            Thanks again.

             

            Ian

            • 3. Re: How to blend data accurately
              Russell Christopher

              Hey Ian -

               

              Unfortunately, no - if there is no "Month" value in the GL Entry table, we have nothing "to compare" against the Budget table..therefore no budget value will come back for that non-existent month in GL Entry.

               

              A couple things you might try:

               

              • Add empty entries for each month in GL Entry
              • I noticed that both of these tables are coming from SQL Server. It appears they live on the SAME server, too. That being said, you should consider joining the tables at the SQL Server, rather than doing so in Tableau via Data Blending - it would be more efficient, too - so you'd get your answers more quickly
              1 of 1 people found this helpful
              • 4. Re: How to blend data accurately

                This relates more to how to accomplish what you want with the SQL query which you can do when you establish the connection and use "CUSTOM SQL" . When you want to join two tables where one of the table values could be NULL, use a LEFT OUTER JOIN like this.

                 

                SELECT B.Month, B.Amount, COALESCE(A.Amount, 0)

                From BudgetTable B

                LEFT OUTER JOIN ActualTable A

                ON A.Month = B.Month

                 

                A regular (INNER) join would return nothing for the months that have no actual values, but using the OUTER join, even when there are no actual values the budget values will still be returned. In that case the A.Amount would be NULL so use the COALESCE function to turn the NULL into zero.

                 

                good luck.

                1 of 1 people found this helpful
                • 5. Re: How to blend data accurately
                  Ian Hutchison

                  Hi Guys

                   

                  Thanks for your suggestions. What I ended up doing was creating a spreadsheet with all the account codes and dates required.

                   

                  Using the spreadsheet as my primary, I managed to get the data pulled over. I have attached my solution.