7 Replies Latest reply on Mar 3, 2017 2:41 PM by Aseem Sharma

# Counting similar text in a delimited string

I have 1 column with a variable length comma separated string eg.

x,a,c,d,f

c,d

d,a,c,y,p,x

a,c,d

d

trying to count the occurrences of each alphabet.  Final result

x = 2

a = 3

c = 4

d = 5

f=1

p = 1

y =1

Anyway to accomplish this in tableau?

• ###### 1. Re: Counting similar text in a delimited string

If you had a string like x,a,x would you need that to count X once or twice?

If you want it to count once, just create a calculation like this for each letter of the alphabet: COUNT(CONTAINS([YourField],'x'))

If you need to calculate multiple occurrences within the same string, create a calculated field for each letter of the alphabet like: SUM(len([yourstring])-len(replace([yourstring],'x','')))

• ###### 2. Re: Counting similar text in a delimited string

x would count as 2.

but x's and a's are dynamic values, so i cant specify them in COUNT(CONTAINS([YourField],'x'))

• ###### 3. Re: Counting similar text in a delimited string

How is Tableau meant to know what you want to count then? Please explain your requirement better and provide a better example of your data as an attached Tableau Packaged Workbook with that sample data.

• ###### 4. Re: Counting similar text in a delimited string

This is a sample data set, column contains a  comma separated string.

String can contain 200 different types of phrases, always comma seperated

Time and a half

Time and a half,zoomtext

Separate room

Time and a half

Time and a half

Separate room,double time

Time and a half

Time and a half

Time and a half

Time and a half

Separate room,time and a half

Separate room,time and a half

Separate room,time and a half

Separate room,time and a half

Time and a half

Trying to Get to

Time and a half = 13

zoomtext = 1

Seperate room = 6

double time = 1

Hope this clarifies things

• ###### 5. Re: Counting similar text in a delimited string

Effectively it sounds like you want to Tableau to scan all rows and extract a distinct list of terms which occur in each row, separated by a comma.

Then for those distinct terms, count the number of instances.

Unfortunately, this is isn't possible. You either need to create calculated fields looking for the instances like I demonstrated in my first reply or you need to preprocess so the strings are separated out onto individual rows. You could write some SQL to do this preprocessing if you're connecting to a database, otherwise you'll need to look into using a script or tool to do that pre-processing some other way.

• ###### 6. Re: Counting similar text in a delimited string

Thanks for looking into this.

• ###### 7. Re: Counting similar text in a delimited string

Couldnt do this in tableau, but used the following sql to split data into multiple rows then added that as a new data source

`SELECT T1.reference, T1.name, T2.my_Splits AS subjects, T1.subtitileFROM (  SELECT *,  CAST('<X>'+replace(T.subjects,',','</X><X>')+'</X>' as XML) as my_Xml   FROM [yourTable] T ) T1 CROSS APPLY ( SELECT my_Data.D.value('.','varchar(50)') as my_Splits FROM T1.my_Xml.nodes('X') as my_Data(D) ) T2`

1 of 1 people found this helpful