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











9 comments:

  1. That's so funny that you came up with this. I just had this exact issue with a dashboard I was creating on Friday.

    ReplyDelete
  2. Pretty cool. Thanks for sharing!

    ReplyDelete
  3. I love this. I have something very similar for building tables in SQL server from demo sheets in excel: Paste in the column names, select the data type from a combo box and it would write the SQL to build the table. It makes a case for keeping vba skills up. Got me thinking of all the other things worth automating in excel...

    ReplyDelete
    Replies
    1. Yeah, I have a few more of these as well. It can certainly save a ton of time. I'd be interested to see what you have.

      Delete
  4. Well tbh I've not been using Tableau that long, but I start a new job Monday where I'll be using it daily. I'm sure I'll build up a catalogue of cheats! I have one I'm working on: out of habit I test any SQL joins etc in MS Access (old habit and I like the gui) but if you try and paste it as custom SQL into Tableau the syntax is slightly different (table designations are different and text strings are ' not "). I'm writing a little vba to clean it up. I just hate repetitive tasks.

    Any ideas that might help me would be welcome!

    ReplyDelete
    Replies
    1. Kevin wrote this blog, but I just want to note that Access definitely uses a different flavor of SQL than most database platforms. Access is not a standard at all. For example, single quotes are standard, but double quotes are not. Not sure why Access implemented SQL in that manner, but the syntax will definitely be a bit different in Tableau (and other database platforms). If you're interested, we have a good series of blogs on SQL on this site as well. Just use the SQL tag/category.

      Delete
  5. Really appreciate this! Great time-saver.

    ReplyDelete

Powered by Blogger.