2 Replies Latest reply on Sep 12, 2016 4:20 PM by Eleanor Green

    Union two data sets to create map

    Eleanor Green



      is it possible to create a union of two data sources with some different columns?


      I want to be able to map the location of power plants against the location of water level measuring points on a river.


      They share the country, latitude and longitude columns but obviously not the other data types.


      I feel like this is possible but having read up on unions and dual axis maps and other things, I still have not made it work.


      I have attached a sample excel file


      Many thanks in advance.

        • 1. Re: Union two data sets to create map
          Dan Sanchez

          Hi Eleanor!


          The solution is a bit clunky but we can definitely union the two worksheets that have different columns.  For this to work though we'll need to use Custom SQL which means connecting to the Excel File using the Legacy Connection.  After connecting through Legacy Connection, drag the new Custom SQL option in the connection screen.  I used something like this to union the two worksheets:


          SELECT ['River measuring points$'].[CORE_DATUM_TYPE_ID] AS [CORE_DATUM_TYPE_ID],

              ['River measuring points$'].[COUNTRY] AS [COUNTRY],

              ['River measuring points$'].[LATITUDE] AS [LATITUDE],

              ['River measuring points$'].[LONGITUDE] AS [LONGITUDE],

              ['River measuring points$'].[PLACE] AS [PLACE],

              ['River measuring points$'].[RIVER] AS [RIVER],

              "" AS [Company],

              "" AS [Fuel],

              "" AS [MW],

              "" AS [Plant_and_unit],

              "" AS [Region],

              "" AS [Status],

              "" AS [Unit_code]

          FROM ['River measuring points$']

          UNION ALL


              ['Power plants$'].[Country] AS [COUNTRY],

              ['Power plants$'].[Latitude] AS [LATITUDE],

              ['Power plants$'].[Longitude] AS [LONGITUDE],

              "" AS [PLACE],

              "" AS [RIVER],

              ['Power plants$'].[Company] AS [Company],

              ['Power plants$'].[Fuel] AS [Fuel],

              ['Power plants$'].[MW] AS [MW],

              ['Power plants$'].[Plant_and_unit] AS [Plant_and_unit],

              ['Power plants$'].[Region] AS [Region],

              ['Power plants$'].[Status] AS [Status],

              ['Power plants$'].[Unit_code] AS [Unit_code]

          FROM ['Power plants$']


          What I've done is create the full domain of columns between the two worksheets.  For a column that exists in one worksheet but not the other, we just tell the custom sql to insert a null value and create that column (the lines with "" AS [column name] are the ones where we create the new column and insert a null value).  We can then bring out the Lat and Long fields from your data source onto Rows and Columns to create the map:



          Hope that helps get you started!

          1 of 1 people found this helpful
          • 2. Re: Union two data sets to create map
            Eleanor Green



            This is super


            thanks so much for your help!


            I had never come across the concept of legacy connections before