2 Replies Latest reply on Apr 9, 2013 7:03 AM by Jonathan Drummey

    Changing NA to 0

    Ajit Kumar

      Hi,

       

      I have an excel file which I have connected to Tableau 7. In the data we have almost 400k rows and 200 columns. In the data we have 100 columns which are Measures and contain "NA".

       

      Is it possible to change NA to 0 in tableau for all the 100 Measures?


      If yes please tell me the way.

       

      Ajit

        • 1. Re: Changing NA to 0
          Brad Llewellyn

          Ajit,

           

          I ran a quick test and discovered this calculated field will work

           

          IF [Value] = "NA" THEN 0

          ELSE INT( [Value] )

          END

           

          However, it's HIGHLY recommended that you do this outside of Tableau.  If you have 100 calculated fields running this IF statement for 400k rows, you will likely suffer a performance hit.

           

          Hope this helps,

           

          Brad Llewellyn

          Associate Consultant

          Mariner, LLC

          brad.llewellyn@mariner-usa.com

          http://www.linkedin.com/in/bradllewellyn

          http://breaking-bi.blogspot.com

          • 2. Re: Changing NA to 0
            Jonathan Drummey

            FYI, if you use a Tableau data extract, there is no performance hit for the IF statement as written by Brad because Tableau will materialize the results of that calculation inside Tableau.

             

            However, with 100 initial measures + 100 calculated fields for the measures + dimensions + other calculated fields there could still be a performance hit because Tableau prefers "tall" data to "wide" data. (Tableau can have some performance limitations as the data sources increase in complexity). In that case, you might look into either creating multiple Tableau data sources (each with a subset of the measures for different analyses) and/or reshaping the data so there are more rows and fewer columns.

             

            Jonathan