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
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
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.
Bump. Still looking for an answer to my second question. Any help would be appreciated. Thanks so much.