1 Reply Latest reply on Jun 19, 2012 5:55 AM by Alex Kerin

    Data field that mixes numbers and text strings

    Raoul Leblanc

      I have a data field (I will call it "% Decline") that mixes numbers (percentages from 0 to 100%) and also contains data points that do not have a particular percentage associated with them but which have a text description explaining why they do not have a percentage.  There are three or four reasons why this might be the case, and I need to have this information.  In Tableau, I am mapping these each datum by its lat/ long.  However, when I try to filter, Tableau is dumping all the text data points into a NULL category.  I want to have the ability to show the various categories of the text description. 

       

      I have tried everything I can think of with the Change Data Type options, making them measures or dimensions, etc.  I have even tried reformatting the original data in Excel, then refreshing my extract.

       

      In addition, I would like the ability to make the colors continuous, with the different text categories their own colors as well, and to make bins with the numerical data.  

       

      Any help would be greatly appreciated. 

        • 1. Re: Data field that mixes numbers and text strings
          Alex Kerin

          Presuming you tried setting the column to text in Excel, you could also try moving one of the rows with special case cells (i.e. one with text as well) up to the top, or just adding text to that first row. JET (MS product that Tableau uses to bring in data from Excel and others) is reading the first x lines of your file and deciding it's a number. That's why all of the text cells end up as null.

           

          If you can't do that for whatever reason, you may have to push the data to a text file and use a schema.ini: http://msdn.microsoft.com/en-us/library/ms709353%28VS.85%29.aspx

           

          This is a bad situation as many users won't realize they have data issues; there's an idea here to get Tableau to implement better error checking: http://community.tableau.com/ideas/1104

           

          Once you get the data in, you could use find(), left(), right() to parse it out.

           

          Another option is to do this in Excel. Either do text to columns on a space, or replace % with something like %~ and text to columns on ~