1 Reply Latest reply on Oct 12, 2016 1:12 PM by Dinesh Sundar Gomathinayagam

    Data Blending Challenges

    Sanjay Mistry

      Hello all,

       

      I am having challenges with blending two different systems. Two scenarios:

       

      1. I am trying to blend two columns - Date Added and Crated Date. Here is how they look:

       

       

      The data won't blend for two reasons - date format is different and there is a gap in the data (nature of business). See Date Added doesn't have Jan 4 but Created Date has.

       

      2. The other option is to blend two columns - Location and NH Office. Here the challenge is that Location has <City><State> and NH Office has bunch of codes and just the <City>

       

      What are my alternatives? How do I blend my data using any of these options?

       

      Thanks,

        • 1. Re: Data Blending Challenges
          Dinesh Sundar Gomathinayagam

          Hi Sanjay Mistry

          For the first question, I have used two options in the past. First, you can use the table/data source with the higher number of dates (making sure it includes all the dates in the other source) as the first table and create a left join on this source to connect other sources. The other option which is more robust and advisable is date table. You can create a date table in your primary data source (or many organizations already have one) and connect all other data sources to the date table.

           

          A helpful link to create a date table in SQL server:

          Creating a date dimension or calendar table in SQL Server

           

           

          For the second question, you need to clean up the data in with of the two data sources to make them match one another. The community may be able to help you better if you are able to provide a more comprehensive list of the "Location" and "NH Office" list.