6 Replies Latest reply on Aug 30, 2016 8:29 AM by chris.moore.11

    Removing Null Values

    Liam Lofthouse

      Hey guys,

       

      Please accept my apologies if this issue has been raised previously.

       

      I'm having some trouble when removing null values from my data. I've followed previous posts on this forum and removed them using the duplicate dimension/filter method with success.

       

      However I've noticed that when removing a null value, it also removes all data from that row in other columns in the original data source. For example should the blank column responsible for the null value be E5, then any neighboring data say A5, B5, C5 etc is also removed when excluding the null value.

       

      Any suggestions on how to prevent this would be greatly appreciated.

       

       

      Cheers,

       

      Liam.

        • 1. Re: Removing Null Values
          chris.moore.11

          Hi, you wont be able to "remove" null values from the data without removing or filtering out the entire record.

           

          However its usually pretty easy to leave the record in and deal with the null values. Maybe if you give an example of how leaving the nulls in is causing an issue with your visualization someone can help.

          • 2. Re: Removing Null Values
            Ankit Mandal

            Did you try creating a 'Set' from the duplicate record and then putting that into the filter to exclude the Null values ?

            • 3. Re: Removing Null Values
              Simon Runc

              hi Liam,

               

              Here's a slide (one of my faves!) from my Tableau training deck (which was 'borrowed' word-for-word by a post contribution from Jonathan Drummey)

               

               

              ...this is also, btw, why Tableau can operate on data-sets in the 10s (or even 100s) of Millions of rows, and Excel struggles with a few hundred thousand!

               

              So as Chris says, when you exclude a value it is the entire row that is excluded, but there are many ways round NULLs (and in fact can be very useful). If you can let us know what you are trying to do, we can (almost certainly!) help.

              • 4. Re: Removing Null Values
                Liam Lofthouse

                Hi Chris,

                 

                Thank you for your response. Here is an example of what it is i'm trying to do:

                 

                I would like to 'tidy' up the data set below. So for instance if i remove the null value on 'Trust Summary CQC Rating' this restricts any further data added to to worksheet to 1 line as shown in the second image. I've attached the worksheet and original data source. 001.PNG002.PNG

                • 5. Re: Removing Null Values
                  Liam Lofthouse

                  Hi Simon,

                   

                  Thank you for sharing this, very helpful indeed!

                  • 6. Re: Removing Null Values
                    chris.moore.11

                    It depends on how you would like to clean that table up. One suggestion would be replacing the "null" with some other text. To do that you could create a calculated field that re-codes the null values to say something else.

                     

                    In the attached workbook the fields; "Recode - Key Priorities moving forward", and "Recode - Trust Summary (CQC Rating)" replace the "null" with the text "None listed". You could change that to say whatever you'd like though.

                     

                    if ISNULL([Key Priorities moving forward]) then "None Listed" else [Key Priorities moving forward] end

                    if ISNULL([Trust Summary (CQC Rating)]) then "None Listed" else [Trust Summary (CQC Rating)] end

                     

                    Hopefully, this helps. Someone may have a better idea on how to clean that table up.