KNOWLEDGE BASE

Error "Numeric value... is not recognized" When Converting Non-numeric Data to a Number Type a live Connected to Snowflake


Published: 26 Jul 2017
Last Modified Date: 20 Sep 2017

Issue

When connected live to Snowflake and attempting to convert a dimension that contains non-numeric data to a number, then the following error occurs:  
 
Numeric value '{field name}' is not recognized​
 
This occurs both when attempting to convert the field to Number (Whole) or Number (Float) in the data pane, or when wrapping the field in INT() or FLOAT() in a calculated field.

Environment

  • Tableau Desktop 10.1.0-10.1.10, 10.2.0-10.2.4, 10.3.0-10.3.2
  • Snowflake

Resolution

Option 1

Upgrade to Tableau Desktop 10.3.3 ; 10.2.5 or 10.1.11. For more information, see Upgrading Tableau Desktop in Tableau Help.

Option 2

Use an extract. For more information, see Extract Your Data

Option 3

Use calculated fields to determine which records contain only numeric data before attempting to convert the data into a number type. The attached example workbook uses sample data to demonstrate the following directions:
  1. Create a calculated field named "Truncated String" with a calculation similar to the following:
    RIGHT( [Customer Name], 5 )
    
  2. Create a Calculated Field called "Remove All Numbers From Truncated String" using the following formula:
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        REPLACE([Truncated String],'1',''),
                                    '2',''),
                                '3',''),
                            '4',''),
                        '5',''),
                    '6',''),
                '7',''),
            '8',''),
        '9',''),
    '0','')
    
    • The above calculation replaces every possible numeric value with an empty string. Therefore if the original string contained only numeric values then the result of this calculation will always be an empty string.
  3. Create a calculated field with a name like "Convert Numeric Truncated String to Number Type" with a calculation similar to the following:
  4. Create a Calculated Field called "CHANGEME" using the following formula:
    IF [Remove All Numbers From Truncated String] = ''
    THEN INT([Truncated String])
    END
    

Cause

This behavior is related to a known issue (ID: 671192) that has been fixed in a recent release of Tableau Desktop. 
Did this article resolve the issue?