7 Replies Latest reply on Jun 15, 2016 3:12 PM by koushik bowinipally

    Count Number of Appearances of a Value in a String Field That Contains Multiple Delimited Values

    Steven N.

      Tableau novice here with a question:

       

      Objective: Count the number of appearances of a value in a delimited string field.

       

      Example Data:

      Country
      US
      US; Canada
      US
      US; Mexico; Canada
      [Blank]
      Mexico
      ...

       

      What I'm getting

      CountryCount
      US2
      US; Canada1
      US; Mexico; Canada1
      Mexico1
      ......

       

      Desired Output

      CountryCount
      US4
      Canada2
      Mexico2
      ......

       

      Attempted Solutions

      > SUM NUMBER OF RECORDS -- gives me the "What I'm Getting" above; unable distinguish each delimited value

      > I managed to get an IF CONTAINS() function to work for one country, but I'm loathe to use it as I would have to program it ~200 times -- one for each country in the world.

       

      Potential Approach

      > If the Country column is insufficient on it's own, I can import a column with a list of all countries (let's call this column List of All Countries). Is there is a function in Tableau where it would look down this List of All Countries column, take each country name, go to my Country Column, and count how many time it appears? Unfortunately, I'm not adept enough (yet!) at Tableau to know if this is possible, never mind how to technically author that function.

       

      Similar Discussion Topics I've Found

      calculated field - Tableau count number of times same value appears in column - Stack Overflow -- I was unable to get this to work since my field potentially has multiple values in each

      The Cross Join Collection  -- #5 appears a potential solution, but I have zero familiarity with SQL

       

      I appreciate any guidance, suggested approaches, or existing solutions one can offer.

       

      Thanks,

      Steven