5 Replies Latest reply on Dec 19, 2017 12:59 AM by Yuriy Fal

    Blending Datasources Based on "contains" rather than "matches exactly"

    Catherine Sturla

      I have a workbook in which I've blended information from an excel spreadsheet and a SQL database using "Edit Relationships."  The issue I'm confronted with is that the string of values in the second datasource often contains multiple values separated by commas.  To be more specific, datasource 1 has a list of 8 digit codes, one code per row of an excel sheet; datasource 2 contains a list of those codes as well, but in certain cases, on line will contain 2 or more 8 digit codes.  As my data relationship is now, any value from datasource one that matches a line in datasource 2 with a long string, shows as a null.  Is there a way to link the datasources on "lookup" or a match based on "contains" a certain value rather than matches exactly?

       

      Example:

       

      Datasource 1:

      Project Code
      Money Raised
      00000011$500
      00000088$100
      10000000$2300

       

      Datasource 2:

      Project CodeProject
      00000011, 00000088Dogs
      10000000Cats

       

      When I blend the tables I'd like to have a table that looks like this:

      Project CodeProjectMoney Raised
      00000011Dogs$500
      00000088Dogs$100
      10000000Cats$2300