Case Statement Generator

NOTE: In September 2020, the Case Statement generator was updated.  Please see the following blog post for more information: Case Statement Generator Improvements & IN Operator Generator.  

I typically use numerous case statements in every single Tableau workbook I create - both in business and in personal work.  Both the concept and the syntax are quite simple.  However, when you are dealing with statements using many values, writing a case statement can be very repetitive.  For every value, you have to list the “WHEN”, then the value in single quotes, the “THEN”, and the result in single quotes.  This is no big deal when you have short case statements, but it takes a while for longer statements. 

After using Tableau for just a couple of weeks, I wanted to create a more efficient way.  That is when I created my Case Statement Generator in Excel.  This Excel template allows you to simply enter the field the statement is based on along with the values and results without any quotation marks.  The template then generates the entire case statement for you and all you have to do is copy and paste it.  This has saved me countless hours. 

Go ahead and download the template using this link.  (Please note that some people have experienced issues downloading from Google Drive, so if you have issues, simply email me at flerlagek@gmail.com and I will directly send you the spreadsheet). Open it up, go to the “Case Statement Generator - Ex 1” worksheet and let’s walk through an example.  Assume that Superstore data included Sub-Category but no Category.  We could simply right-click on Sub-Category and click on Describe to show the details of that field.  At the bottom, under Domain, you will see a list of all the values within this field.  From here, you can select and copy those values then paste it directly into the WHEN column of the Case Statement Generator.  Next, you fill out the THEN values (in the second column), and ensure that you fill out the field that the case statement is based upon (cell A9).  When you do this, the Excel worksheet will write the calculation for you by stringing the components together, inserting quotation marks, spacing, etc.  In the green area, will be the final case statement.  All you have to do is simply copy it and paste it into a Tableau calculation window. 

What about situation where you want to abbreviate the US States?  I simply did a quick Google search for States and Abbreviations and found a list in a few seconds.  I then copied and pasted those values directly into the Excel spreadsheet and that’s it!  It literally took me 15 seconds to create this case statement to convert State to State Abbreviation. 

There is nothing mind-blowing about this blog post and this spreadsheet, but download the template, try it and see if it saves you some time.  The template itself includes both of the examples I mention in this short blog post as well as a blank template. 

Thanks or reading and let me know how the template works for you!

Kevin Flerlage, December 23, 2019 | Twitter | LinkedIn | Tableau Public