5 Replies Latest reply on Aug 8, 2018 1:52 PM by Charlyne Lees

    Check if Field is Numeric?

    Charlyne Lees

      I'm trying to build a calculated field that checks if a string is a specific letter followed by 4 numbers. The string can contain all letters or a mix of alphanumeric characters.

       

      I've tried to do ABS(INT([field])) as I've found elsewhere on this forum, however it's not working as expected.

       

      For example, the string "N123" correctly returns a "0" as it's not numeric, however, "123N" returns "123" even though there's a letter in the field.

       

      RegEx is not an option with my data source (MySQL).

       

      I am on Tableau 2018.1.3

        • 1. Re: Check if Field is Numeric?
          Ken Flerlage

          This seems to work for me. You're doing this calculation within Tableau itself?

           

          In the attached, I've created a parameter that lets me enter any value. I then use the formula ZN(ABS(INT([Value]))). It converts both 123N and N123 to 0 and numeric values to the actual numeric value.

           

          Any chance of uploading a packaged workbook?

          • 2. Re: Check if Field is Numeric?
            Charlyne Lees

            Yes, within a calculated field in Tableau

             

            I'm taking my field, stripping off the first character (which will always be a letter), then grabbing the next 4 characters and trying to see if they're numeric.

             

            My result is this, with the first column being the result of the "RIGHT(LEFT([Supplier Code],5),4)" calculation and the second column being the result of the above calculated field.

             

            • 3. Re: Check if Field is Numeric?
              Brandon Kidwell

              Hello Charlyne,

               

              I found this works in Tableau for me as well however if you're trying to isolate the numeric you can create a calculation such as the below and filter out the "Alphanumeric" to show only the numeric values.

               

              TRIM(IFNULL(str(INT([Field])),"Alphanumeric"))

              • 4. Re: Check if Field is Numeric?
                Ken Flerlage

                Strange. It's not doing that for me. In fact, it doesn't convert those alpha fields to 0 either--it converts them to NULL. I'm wondering if this might be a data source thing. Are you using a live connection to MySQL? If so, can you try creating a Tableau extract?

                • 5. Re: Check if Field is Numeric?
                  Charlyne Lees

                  Yes, I'm currently using  a live MySQL connection. I can create an extract, it just seems odd that it would calculate them differently based on data source. If I replace the field with the same "256S" string as the data set, I see "null" but the data set returns as shown above.