Counting Problem

[urreacp]

Hello,

Much thanks in advance.  Here is the basic data set I'm working with:

Provider Interventions Date
1                             A;B;C                       ...
2                             B                              ...
3                             A;D;E                       ...
4                             B;E                           ...

I need to count the number of interventions total - each letter represents one intervention. Some lines will have more than 1, separated by a semicolon (as shown). The total interventions for this sample would be 9. My issue lies with counting the interventions separated by semicolons. I considered counting the number of semicolons per line and adding 1. Is there a way to do that? Any solution at all would be wonderful.

Thanks so much,
Christian

[New_Guy]

Hi,
Try the below and see if this helps, another option is the replace function if you can get it to work. Add a condition for null values if you expect null values in the interventions column.

case when ((char_length ([Data Item1]) -1)/2) = 0 then 1 else ((char_length ([Data Item1]) -1)/2) end

Corrected

case when char_length ([Data Item1]) = 1 then 1 else (((char_length ([Data Item1]) -1)/2) + 1) end

Good luck
New guy

[urreacp]

Hello New_Guy,

I should laid out the example data a little better. The interventions won't be single letters, they are strings of various lengths.  I managed to use the replace() function at your suggestion.  Then I compared the string length before and after removing the commas to get the result. Worked like a charm. Thanks so much for that!

Now I have another question though. How will I be able to tally up the amount of each individual intervention in a crosstab? Normally if there were 1 intervention per line it would be a very simple crosstab summary. But with this setup I'm a little confused.

Thanks again for your assistance.

[urreacp]

Bump. Still looking for an answer to my second question. Any help would be appreciated. Thanks so much.