2 Replies Latest reply on Feb 19, 2012 9:31 AM by grantreeves

    Blending Data on Date Fields in Two Tables from Microsoft SQL Server

      I am in desperate need of some help.

       

      I am trying to blend two different tables from the same data source; its a Microsoft SQL Server in my case.  These two tables include Sales data that is pulled in and warehoused from our AIS.  The fields in both tables are EXACTLY the same.  All dimensions and measures are 1:1. 

       

      Our company operates in say......5 business segments.  Prior to January 1, 2012 we had separate sales tables for each segment.

       

      For example:

       

      SalesHistory_BusinessSegment1

      SalesHistory_BusinessSegment2

      .....

      SalesHistory_BusinessSegment5

       

      All Sales tables have exact same fields and dimensions. 

       

      Also, in our AIS, each Business Segment had a different company.


      On January 1, 2012, we collapsed the companies into one in our AIS which then gave us the ability to put all sales data in 1 table.  We can call it SalesHistory_AllBusinessSegments.  Also, prior to 2012, we had a certain G/L structure, and in 2012 when we collapsed the companies we had to redo our G/L.  So we're working with two fields with the same name but different data (GLCode is the field).

       

      I am attempting now to for instance take SalesHistory_BusinessSegment1 and blend it with SalesHistory_AllBusinessSegments and I, for the life of me can't get the dates to join correctly.

       

      I am creating a full join on GLPOSTDT.  When I do this, and I pull my Customer ID's on to the row shelf and pull my GLPOSTDT to the column shelf, it shows 2008,2009,2010,2011,null.  I then pull my GLPOSTDT from my new table in and it shows null,null,null,null,2012.  Imagine the pills right beside each other on the column shelf.

       

      What am I supposed to do here!?!?  Can anybody please help me!?  Any help would be so greatly appreciated.

       

      Thank you!

       

      Grant

        • 1. Re: Blending Data on Date Fields in Two Tables from Microsoft SQL Server
          Richard Leeke

          If I'm understanding correctly how the data is structured I think you will need to define a custom SQL connection giving you the union of all of your tables, rather than trying to use data blending - you can't use data blending to merge data from different years like that.

           

          A union query will work easily if your table structures are identical - i.e. if they have the same number of type of fields and the same field names. If there are slight differences you might have to do some slight tweaks.

           

          The easiest way to generate the custom SQL query if the tables are indeed identically structured is just to selecting one of the tables you want as a single table connection, then switch over to a Custom SQL connection. That will be pre-populated with a query for all fields in that table. Coopy the text of that query into a text editor and append a line saying "UNION ALL".

           

          The switch back to your Tableau window and change the connection back to a single table connection. Select the second table you want to merger, then change the connection back to a Custom SQL connection. Copy the SQL statement for the second table and paste it onto the bottom of your first one, appending another "UNION ALL" line.

           

          Keep going till you have a query which merges all of your tables (you mustn't have a UNION ALL line after the last table).

           

          Finally copy the query and paste it into the Custom SQL window and complete defining the connection.  Be warned that if there are any differences or you make any slight mistakes in the editing you may get fairly misleading erorr messages.

          • 2. Re: Blending Data on Date Fields in Two Tables from Microsoft SQL Server

            This worked perfect.  Thank you so much!!

             

            There were actually two or three extra fields in the new tables that I didn't know were added.  However, all I did was erase them from the Custom SQL code and it worked great.  Now I see all data for 2005-2012 in the same table!!

             

            Thanks again.

             

            Grant