5 Replies Latest reply on Aug 30, 2017 4:09 AM by Norbert Maijoor

    Need Help: Counting Sub-strings Across Two Data Sources (LOD?)

    Aaron Weiss

      I have a list of Reports that are each associated with multiple countries.  Based on the data source this is coming from (SharePoint List) all the country information is captured in a single field for each record.  The number of coutnries associated with a report can vary from one (1) to 100 or more.

       

      What I would like to do is count the number of times a country name appears in that field.  The ideal output would look like this:

       

      Country NameCount
      United States2
      Panama4
      Colombia1
      Etc.

       

       

      I've attached a sample workbook that has two data sources:

       

      1. Complete country list

      2. Data from SP with Report ID and concatenated country field.

       

      I'd appreciate any help figuring out how to use the list in data source one to generate the output desired.

       

      I've been playing with COUNT and LOD but can't seem to get this to work. 

       

      Thanks,

      Aaron