Actually this is too easy in 10.2 and above, You just need to use a Cartesian Join on 1:1 and a Count. So Get a List of Cities from Net , then do a Cartesian Join with your data and then use a count to get this.
If it Helps, Pl mark it Helpful and CORRECT to Close Thraed
Ignore Duplicates in String.twbx 15.8 KB
Deepak's solution works excellently. I wanted to see if this could be done without the city list being created, and without modification of the original file.
This can be done in custom SQL or in my case Tableau Prep.
Basically you take the source (Excel), split the city into separate city columns, pivot those together to get a column of just individual city names (but with lots of repetition), remove all but this column, use the aggregate grouping to just get distinct rows resulting in a list of cities, add an ID for the join.
Join this in the same way as Deepak does with another copy of the file, add the ID, left join, use a similar calculation as Deepak to check for the city name, then aggregate again to the original rows and the countd of cities.
You could also try a RegExp replace like this:
and then count the commas in the result of that.
Although the above regexp doesn't quite correctly remove all
the duplicate words (there is a residual "New"), it should remove
all the commas associated with a duplicate, so the count should work.
Please see workbook v10.3 attached in the Forum Thread.
275348dup.twbx 4.8 KB