6 Replies Latest reply: May 26, 2015 2:30 PM by Travis Russo RSS

    Find Substring, and link to another field

    Travis Russo

      Hello,

       

      I'm sorry if this is confusing, but it's a bit difficult to phrase. I am looking to search within a field, and if a match is found, return another field from the substring. Below are a fewexamples. The values I'm searching in are not standardized, meaning not the same format with underscores or something similar.

       

      DataSet:

      TerritoryCoast???
      Sales_NorthEast_123 from MatchTable
      Manager_xyz_West_012from MatchTable
      Manager_Growth_Northeast_123from MatchTable

       

       

       

      MatchTable:

      if Territory contains...then Coast is...
      NortheastAtlantic
      WestPacific

       

      Final Dataset (Coast is the field I'm looking for)

      TerritoryCoast
      Sales_NorthEast_123 Atlantic
      Manager_xyz_West_012Pacific
      Manager_Growth_Northeast_123Atlantic

       

       

      Does this make sense? Thanks for any help!

       

      EDIT - I should have mentioned that there are many records to match from, and I don't want to hardcode the cross-ref as this dashboard will be used for many years and would like it to be able to handle new organizations.

       

      Thanks for your feedback thus far. Much appreciated.