Tableau Contains Query

Question

I have a list of strings (labels) in format like: "go-classmate, go-atc, go-x" "go-2, go-1, go-y" "go-classmate, go-cc, go-z"

I want to get count of for example label go-classmate. I wrote a following query:

if CONTAINS([Labels], "go-classmate") then 1 else 0 END

And added it as column. In a row I have list of labels. So now I am getting some bizzare results. For example for the string: askatc-request,go-canvas,go-classmate I am getting result 2. It should be never more than 1.

What am I doing wrong?

Answer 1

You have two rows in your data set with label of askatc-request,go-canvas,go-classmate. Hence, the sum of 2 for that particular label.

Answer 2

Don’t cram multiple values into a single cell in your data — even if you stick commas between the terms.

That might be the way your data is handed to you, but it’s almost always worth the time to reshape your data to fix this problem. Any time you spend splitting the data apart to get at least to second normal form will pay off in much simpler and effective analysis. If you have a max # terms that can appear, you can reshape easily in Tableau using split transformations and pivots. Otherwise, reshape upstream.

So my answer is don’t try to paper over the issue in the visualization phase, fix the underlying problem in the data representation. You haven’t shown enough of your data schema to allow a detailed response, but the suggestion is to turn each of your original data rows into multiple rows in your transformed source, where if a data row has N terms originally, it turns into N copies of the row in the new structure, each citing a single term.

Then the analysis and visualization should be almost trivial, with the only trick being the need to avoid double counting any of the replicated columns.