3 Replies Latest reply on May 26, 2016 12:56 PM by courtney lopez

    Database shows cells with letters in account # as null

    courtney lopez

      I am trying to do an equation in tableau that counts the # of distinct account numbers in my database: CNTD(Account Number). The database has some account #s including all numbers and some include numbers and letters. The account #s with letters are showing up as null and not being counted. The data in that column is by default set as "Numbers". I tried converting it to "String," but then it converts all of the account #s without letters to exponents, so the count function still doesn't work. How do I work around this?

        • 1. Re: Database shows cells with letters in account # as null
          Ashish Chaudhari

          Hi Courtney,

           

          Please try change the datatype of the column to string. I know you have also tried converting the column to string but was using a calculated field or through default data type? Can you post some of those account numbers here so that I can try that out? I am sure that number data type is creating a problem for you.

           

          In tableau, please try changing the data type of the column as show below. This will CountD all the items in the column.

           

           

          I also want to know the what is the source of data (excel,access or sql server etc)? If you have text, number and special characters in your column then by default it should be string and not a number. If possible change the datatype of the column at the DB level if it doesn't work at tableau level.

           

          Let me know if that helps.

           

          Thanks and Regards,

          Ashish Chaudhari

          • 2. Re: Database shows cells with letters in account # as null
            Mahfooj Khan

            Can you tell me the datatype of your "Account Number" field? Its alphanumeric I guess.

            While connecting to tableau you should check the data types for all the fields with proper formats, assigned to the fields or not. At data source level you should do fix the formats.

            Mahfooj

            • 3. Re: Database shows cells with letters in account # as null
              courtney lopez

              The source of the data is excel. I tried changing the data type of the column like your example and it did the same thing as when I changed the data type on the data source page.  In excel the data type was number. I tried changing it to text, but it didn't seem to affect anything on tableau by doing that. Here are some examples of the account numbers:

               

              373187799987

              100111768139

              17N4UNA

              17N8D9U

              6753362118

              351214899996

              6605791118

               

              Thanks!