3 Replies Latest reply on Aug 2, 2016 3:49 AM by Carl Slifer

    Discard rows with at least one missing value

    Dmitriy Ratushnyak

      Hello.

       

      Let's say we have a big table with sales information (shop_id, customer_id, purchase_date, item_id, etc). We group it so we have sales amount by year (columns) and shops (shop_id, rows). But there are some shops that were closed or, vice versa, some new stores, so for them some cells are empty, since there were no sales during that year.

      The question is, how to hide rows (shops) that have at least one missing value, i.e. at least one year with no sales?

       

      Sorry if this question was answered before - couldn't find the solution.

      Thank you!

        • 1. Re: Discard rows with at least one missing value
          Carl Slifer

          Hi Dmitriy,

           

          You could place each of them onto the filters shelf and then go to the special table and choose 'non-null values'. However you might want to make a calculated field that flags them all at once and uses a single filter instead of several filters for sanity sake. This may not be the quickest approach but its the first one that came to mind for me.

           

          SUM ( INT(ISNULL([FieldA])) + INT(ISNULL([FieldB)) + INT(ISNULL([FieldC])) .... ) > 0

           

          This checks if any field is a null. If it is a null it will return true. And then wrapping it in INT returns a 1 if it is true. Hence if any value is a 1 (there is a null) then the sum will exceed 0 which means you can filter it off based on your business logic.

           

          Best Regards,

          Carl Slifer

          InterWorks

          1 of 1 people found this helpful
          • 2. Re: Discard rows with at least one missing value
            Dmitriy Ratushnyak

            Hi Carl,

             

            Thank you for your reply.

             

            Yep, I thought about kind of calculated field, but the actual problem is that the input data is just a flat file,  'one purchase per row'. So we get a pivot table, grouping by shops (rows) and year (columns), having total sales in each cell for specific shop in specific year. So if we want to do filtering with creating a calculating field, we need to create it with the pivot table. Is it possible?

             

            Thanks.

            • 3. Re: Discard rows with at least one missing value
              Carl Slifer

              Hi Dmitriy,

               

              Not sure what the file structure you are putting into tableau is but if its something like this

              Category     FieldA     FieldB     FieldC 

              A                                       12              

              B                        100

              C                                                      50

               

              Then the function I've posted will work.

              If you have the fields shops, year, and sales then it would behove you to bring the data into tableau like:

              Shops     Year     Sales

               

              You would only have three columns. Depending on the structure you are being sent you may be able to use tableau's pivot function to make this easier.

               

              Best Regards,

              Carl Slifer

              InterWorks