9 Replies Latest reply on Mar 6, 2012 10:20 AM by Tom Bash

    Create calculated number field based on text field

    Tom Bash

      I have a column filled with text, X, and I want to create a calculated field Y that is a different number or NULL based upon the text in column X using a formula such as:

       

      if find([X], "asdf")>0 then 1.99

      elseif find([X], "xyz")>0 then 4.99 end

       

      However, column Y ends up always being equal to 0 or NULL. I thought I may have to explicitly cast the result as a number but Tableau recognized Y as a number.

       

      Looking at the logs to see the underlying query (I'm using Vertica on the back end), Tableau is using a case-when function to determine the value of Y based on X. I used to do the exact same thing with the data in Excel and it worked fine, so I assume it has to do with the query being generated (as opposed to using the IF statement in Excel before).