4 Replies Latest reply on Sep 12, 2018 1:15 PM by mike.dale

    Count String Matches from a String in Different Data Source

    mike.dale

      Hi All,

       

      I have two tables from two different datasources. I have not joined or blended them:

       

      Library

       

      IDName
      111John
      222Mike
      333Pete
      444Tony

       

      Personnel

       

      IDStaff
      AAA;Rose;David;John;
      BBB

      ;Pete;Mike;Patrick;Agnes;Helen

      CCC;Kim
      DDD;Michelle;John

       

      I want the to count the number of matches there are in the Library Name field with the Staff field in Personnel table.

       

      The deseired output in the Personnel table.

       

      IDNamesMatches
      AAA;Rose;David;John;1
      BBB

      ;Pete;Mike;Patrick;Agnes;Helen

      2
      CCC;Kim0
      DDD;Michelle;John1

       

      Hope this make sense.

       

      Unfortunatly there is no field which I can use to join the two tables. But not sure if this will be required to do this anyway.

       

      And splitting and pivoting the Personnel table is probably not possible as the Names field in this table has in some cases a lot more than 10 names in it.

       

      Thanks in advance.