7 Replies Latest reply on Jul 18, 2016 2:16 AM by Mark Fraser

    FLOAT problem

    paolo.raia

      Hi there,

       

      I have the following data-set which is SQL based:-

       

      123456

      8678768786

      232

      323232

      ABC12

      SDSDFF

      23DED4

      23232

       

      I want to determine if the value is purely numeric (similar to Excel's ISNUMERIC).  I have tried the following FLOAT expressions, but this causes a "Reconnect to SQL Table" loop issue:-

      FLOAT([FieldName])

      ISNULL(FLOAT([FieldName]))

      ATTR(FLOAT([FieldName]))

      ZN(ISNULL(FLOAT([FieldName]))) - to try and force it to zero

      REGEX apparently only works with non-SQL data sources

      ISNULL(SUM([FieldName])) - to allow it to error

       

      Help!!!

       

      Kind regards.

        • 1. Re: FLOAT problem
          Jessica Fan

          Hi Paolo,

           

          This appears to be a database connection issue, unrelated to the calculation you're doing in Tableau.

          Can you check your connections.

          Are you using a live connection to your database? If so try using an extract

           

          I mocked up something similar and can do the calculations without any issues.

           

           

           

          Jessica

          • 2. Re: FLOAT problem
            paolo.raia

            Hi Jessica,

             

            Many thanks for replying.

             

            I need the connection to be 'live' as data is polled periodically throughout the day.

             

            Is there any other workaround, other than having to manually extract the data throughout the day, which would be counter-productive?

             

            Many thanks again.

            • 3. Re: FLOAT problem
              Jessica Fan

              Hi Paolo,

               

              I am also using a live connection in my example, so I think the issue lies with your database

              Have you connected to this database before? Can you connect to other tables, or perform other actions on it without getting the "reconnecting" error?

              How much data are you working with?

               

              Jessica

              • 4. Re: FLOAT problem
                paolo.raia

                Hi Jennifer,

                 

                Yes, I have been connecting to the database without any issues in the past and have multiple dashboards feeding from it.

                 

                However, I have spotted the following error whilst testing:-

                ERROR CONVERTING DATA TYPE VARCHAR TO FLOAT.

                 

                Not sure what this means.

                 

                Regards.

                • 5. Re: FLOAT problem
                  Jessica Fan

                  Are you doing the type conversion in your database or in Tableau? Because that is SQL error, unrelated to Tableau

                  What is the column type of your data in your db table?

                  Are you connecting via a custom sql query?

                  • 6. Re: FLOAT problem
                    Ivan Young

                    Hi Paolo,

                    The error occurs because you are trying convert a letter to a numeric float value.  I believe REGEX is available for SQL databases that support REGEX, SQL Server does not while Oracle does.  If you create a Tableau data extract I believe you can use REGEX as well

                     

                    I'm not sure there is a great Tableau based solution for this, all I can think of is 26 contains statements to filter out all the numeric characters.   If you are using SQL server you could try to identify the records in a view or perhaps with Tableau custom SQL using the PATINDEX function.

                     

                    Regards,

                    Ivan

                    • 7. Re: FLOAT problem
                      Mark Fraser

                      Hi Paolo

                       

                      To link ideas with the thread

                      https://community.tableau.com/ideas/1849

                      and

                      https://community.tableau.com/ideas/4651

                       

                      Cheers

                      Mark