5 Replies Latest reply on Jan 31, 2013 6:17 AM by Andrew Watson

    Multiple Data Sources

    Alexander Rom

      I am trying to combine fields from 3 different Data Sources. They do not share a "Like" field, so-to-speak. I researched the method in the tutorials and it is not what I am looking to execute.

       

      I have 3 different Data Sources set up. I want to compare similar items against each other, they are named differently but are all describing similar things. I can't blend the data and I can't create a calculated field combining the data.

       

      Is there a work around of this? It's becoming a hindrance trying to combine multiple data sources into a single slide in an easy and effective manner.

        • 1. Re: Multiple Data Sources
          Russell Christopher

          Hi Alexander -

           

          To correctly compare fields from different data sources, you need to relate them - the only real way to do this is via fields which contain "like" data: For example Order IDs, Region Names, Dates, etc.. The fields don't necessarily need to have to have the same field names across all sources, but they must have values which match each other. For example, you could blend 3 data sources with the following names with no problem:

           

          • Order ID
          • OrderID
          • MyWonderfullOrderIdentifcation

           

          When your field names don't match you can take control of how mapping should work from the Data | Edit Relationships menu item.

           

          Depending on your data source, you might also be able to write some SQL which actually combines all three fields into a single field with a "UNION". This is an advanced technique, however and doesn't really have much to do with Tableau. Here are a few pointers to discussions on this technique:

           

          http://kb.tableausoftware.com/articles/knowledgebase/union-related-data-across-multiple-excel-tabs

          http://community.tableau.com/thread/114902

          http://community.tableau.com/thread/122322

           

          Hope this helps!

          • 2. Re: Multiple Data Sources
            Alexander Rom

            hey,

             

            I am trying custom SQL. I am attempting to take the similar columns within each field and name them the same thing, so "Total Cost" from one Sheet becomes "Cost", Sheet 2 calls it "Final Cost" again "Cost" etc... for 3 sheets

             

            I continually get this error message. "Database error 0x80040E14: The number of columns in the two selected tables or queries of a union query do not match"

             

            What am I doing wrong? Ideally, I want to remove all other columns and only focus on a single column from each sheet.

            • 3. Re: Multiple Data Sources
              Andrew Watson

              Can you post the SQL you've written?

              • 4. Re: Multiple Data Sources
                Alexander Rom

                SELECT [SHEET1$].[Click Rate] AS [Click Rate],

                [SHEET1$].[Creative Name] AS [Creative Name],

                  [SHEET1$].[Date] AS [Date],

                  [SHEET1$].[Face Value] AS [Face Value],

                  [SHEET1$].[Media Cost] AS [Cost],

                  [SHEET1$].[Placement] AS [Placement],

                  [SHEET1$].[Venue] AS [Venue],

                  [SHEET1$].[ Purchased] AS [ Purchased],

                  [SHEET1$].[Week of] AS [Week of]

                FROM [SHEET1$]

                UNION

                SELECT [SHEET2$].[Actions] AS [Actions],

                  [SHEET2$].[Ad ID] AS [Ad ID],

                  [SHEET2$].[Ad Name] AS [Ad Name],

                  [SHEET2$].[App Installs] AS [App Installs],

                  [SHEET2$].[Campaign] AS [Campaign],

                  [SHEET2$].[Clicks] AS [Clicks],

                  [SHEET2$].[CPC] AS [CPC],

                  [SHEET2$].[CPM] AS [CPM],

                  [SHEET2$].[CTR] AS [CTR],

                  [SHEET2$].[Date] AS [Date],

                  [SHEET2$].[Frequency] AS [Frequency],

                  [SHEET2$].[Impressions] AS [Impressions],

                  [SHEET2$].[Label] AS [Label],

                  [SHEET2$].[Page Likes] AS [Page Likes],

                  [SHEET2$].[Reach] AS [Reach],

                  [SHEET2$].[Social Clicks] AS [Social Clicks],

                  [SHEET2$].[Social CTR] AS [Social CTR],

                  [SHEET2$].[Social Impressions] AS [Social Impressions],

                  [SHEET2$].[Social Reach] AS [Social Reach],

                  [SHEET2$].[Spend] AS [Spend],

                  [SHEET2$].[Unique Clicks] AS [Unique Clicks],

                FROM [SHEET2$]

                UNION

                SELECT [SHEET3$].[Ad group] AS [Ad group],

                  [SHEET3$].[Avg# position] AS [Avg# position],

                  [SHEET3$].[Campaign] AS [Campaign],

                  [SHEET3$].[Clicks] AS [Clicks],

                  [SHEET3$].[Cost] AS [Cost],

                  [SHEET3$].[Day] AS [Day],

                  [SHEET3$].[Impressions] AS [Impressions],

                  [SHEET3$].[Keyword] AS [Keyword],

                  [SHEET3$].[Keyword state] AS [Keyword state],

                  [SHEET3$].[Max# CPC] AS [Max# CPC],

                  [SHEET3$].[Quality score] AS [Quality score],

                  [SHEET3$].[Spend] AS [Spend],

                  [SHEET3$].[Status] AS [Status],

                  [SHEET3$].[Week] AS [Week]

                FROM [SHEET3$]

                • 5. Re: Multiple Data Sources
                  Andrew Watson

                  Yes, that SQL won't work. For a Union statement you need exactly the same field in each Select. Here you don't and it looks like you won't be able to with the data you have. It looks like you might have to do some joining on the Date field instead of trying to Union this - assuming you're trying to return multiple different fields from each sheet.

                   

                  It's hard to advise further without seeing the actual data. Perhaps it would be useful for you to do some basic SQL training Try this site to start: http://www.w3schools.com/sql/default.asp