2 Replies Latest reply on Oct 31, 2016 9:19 AM by gene shackman

    Reading in table with text and numbers

    gene shackman

      I'm reading in a table with text and numbers, like the table below. When I read in the excel file, the two data columns are read in as text. If I use the data interpreter, the cells with "s" are null, and the "*" disappears. I'd like to read in the numbers as numbers. Any help? Do I need to create separate columns with the symbols only, and the number columns have numbers only?

        

       

      groupfrequencypercent
      Wild Red Hair1814%
      Tame Red Hair129%
      Short Black Hair2720%
      Long Black Hairss
      Short Blond Hair3627%
      Long Blond Hair4232%
      Curly Brown Hairss
      Straight Brown Hair6*5%*
      No Hair5*4%*
      s: data suppressed, did not meet reporting criteria.
      *: few cases, rate is unstable

       

       

      Thanks

        • 1. Re: Reading in table with text and numbers
          Carl Slifer

          Hi Gene,

           

          You do not need the percentage symbol to be brought in into Tableau. You have two solutions here. 1, let tableau calculate the percentage - this is probably the most favourable solution. 2 format it as a decimal so tableau will understand that it can be used as a percentage.  If the * means that there are not enough cases then, within tableau you can create a metric that checks if the frequency is high enough. If not you can flag it to be * in another column/comment/colour created entirely within tableau.

          IF [frequency] <=8 then 'Few Cases, Rate is Unstable' ELSE 'Enough Cases to be Stable' END

           

          Finally the data interpreter sounds like its doing a rather good job. if the s is stating the data is suppressed and tableau is treating it is a null that's pretty good. If you must you can create a field that checks that.

          IFNULL([frequency]) and you can filter those out of the view.

           

           

          What sounds like the case is you are using a table that someone has already aggregated. What would be most ideal is to have the raw data that this table is created from, do you have access to this data? Further you can change the datatype that tableau reads. Right click on the field in the dimensions pane and say 'change data type' and use 'integer' Then right click it and make it a measure (or drag it to the measures pane).

           

          I'd either get access to the raw data or I would remove the percentage column and let tableau create that. Further I'd get the actual values instead of the symbols in for frequency and I would create a calculated field to tell me if the group in question is flagged for not meeting the report criteria or if it's unstable.

           

          Best Regards,

          Carl Slifer

          InterWorks

          • 2. Re: Reading in table with text and numbers
            gene shackman

            So, basically, I cannot read in the data as I've posted, and have Tableau read in some as numbers and some as text? If Tableau cannot do that, then I will look at alternatives.

             

            Thanks.