10 Replies Latest reply on Jun 12, 2017 8:51 PM by Justin Arnold

# Counting the number of occurrences of a particular string, then applying weight

How do I write a calculation to:

1. Count the number of occurrences of certain strings appearing in a column? For example, if either '6940' or '6941' or '6942' were to appear in column X, then count.

2. Then, for each of those rows that was counted, apply a multiplier unique to each of those counted rows from another column? For example, if a row has  '6942' in column F14 then count it, but also multiply it by the number in another column of that row for example 3, so if '6942' were present, it should count as 3 instead of just 1.

I am not even sure if this is the order one would do this or if first the weighting should be applied, then count. Thank you!

• ###### 1. Re: Counting the number of occurrences of a particular string, then applying weight

Could you post an example spreadsheet of data with notes how you would want Tableau to operate on it?

• ###### 2. Re: Counting the number of occurrences of a particular string, then applying weight

I would set a value in a calc indicating that a target value was found.

If [X] = 6940 or [X] = 6941 or [X] = 6942 then 1 END

Of course, you wouldn't be hard-coding those values.  You must have them in some variable like a parameter.

But then SUM([that calc]) would tell you how many times one of those three numbers showed up.

And then you could use that calc to do other calcs.

IF [that calc] = 1 then (multiply one thing by another...)  END

• ###### 3. Re: Counting the number of occurrences of a particular string, then applying weight

BTW, I agree wholeheartedly with Zach here.  All I could do is talk about it conceptually.  If that's good enough, then OK.  But if you need specific calcs, they are going to have to be tailored to your example.

• ###### 4. Re: Counting the number of occurrences of a particular string, then applying weight

Each row represents a different deidentified hospitalized patient, but the data set I have only contains ~50% of relevant patients. In order to create an estimate of all hospitalizations in a particular year, each row (patient) needs to be multiplied by its corresponding value in column D. I then need to identify the number of patients (each having been weighted appropriately by its multiplier in column D) that have been hospitalized for various groups of diagnosis codes from column K. For example, say '65421' and '96972' represent diagnosis codes related to psoriasis, I need to count the total number of times either of these strings occur, and then generate a national estimate of the number of psoriasis admissions by multiplying each patient by their corresponding weight in column D.

Specific advise on the easiest way to do this calculation/filter would be appreciated. Thank you Zach and Joe!

• ###### 5. Re: Counting the number of occurrences of a particular string, then applying weight

I'll help if I have a sample workbook to work with.  Otherwise I can't wrap my head around all the moving parts.  Sorry.

• ###### 6. Re: Counting the number of occurrences of a particular string, then applying weight

Justin -- That's not a workbook.  It's an excel file, and it doesn't even have column names.

And attach it to this thread.  I got a security ping for a foreign email attachment.

To attach a workbook on the forum, follow the instructions in this thread:

1 of 1 people found this helpful
• ###### 7. Re: Counting the number of occurrences of a particular string, then applying weight

(Tableau V 10.1 here)

OK.  Now we're off and running!

Tell me about this data.  Are the various Diagnoses pertaining to individual patients?  I don't see any sort of patient # field.

Depending on what this data means, we will probably want to re-shape it.  Depending on what this layout means, the shape of the data will making our task of scouring it easier.

Also, where will the search value(s) be coming from?

• ###### 8. Re: Counting the number of occurrences of a particular string, then applying weight

Great!

Yes, each row is a different patient, and the diagnosis codes are those that they were diagnosed with during their stay. There are no patient #s or identifiers, just different rows.

The search values are predetermined by the clinical question we're trying to answer. The diagnosis codes we are using are relatively rare and require going through the entire database which has millions of rows of data and takes a while.

Thank you.

• ###### 9. Re: Counting the number of occurrences of a particular string, then applying weight

OK, so you have 30 diagnoses in a row, with nothing identifying a specific row.

The reason I was looking for some specific identification per row is that we could pivot the data under that ID, and then it would be easy to scan through the diagnoses with one statement.

But the way this data is set up, all you can do is nest checks on each individual field.

IF CONTAINS([Diagnosis1],"Whatever")

or CONTAINS([Diagnosis2]."Whatever")

or ...

1 of 1 people found this helpful
• ###### 10. Re: Counting the number of occurrences of a particular string, then applying weight

Thank you Joe! I really appreciate your help. Sorry it took me a while to thank you, I was out of town for a bit.