5 Replies Latest reply on Aug 9, 2016 10:17 AM by Joshua Milligan

    Filters while joining tables

    anuashka sharma

           Hello,

       

      Is there a way in tableau to drop columns while joining two tables?

       

      The two tables that I am trying to join, have a huge number of columns and when joined, use up a lot of memory space. I end up getting spool space error. So I was hoping to drop unwanted columns from each table before or while joining. Can it be done in Tableau?

       

       

      Thanks

        • 1. Re: Filters while joining tables
          Tom W

          Yes. In the connection dialog you can 'hide' the columns you don't need by clicking the drop down to the right of the column and selecting hide. It won't be SELECTED into the data set if you do this.

          • 2. Re: Filters while joining tables
            Phillip Overpeck

            In addition to hiding the fields as Tom explained, if you are creating an extract, you should also consider using the aggregate to visible dimensions option. This will aggregate your data up to on the data fields you aren't hiding. That will drastically reduce the size of your data set if you are hiding fields with high granularity. You can also aggregate data fields if you know you don't need it below a certain level of detail.

            • 3. Re: Filters while joining tables
              Joshua Milligan

              Phillip's tip is a great one, with one caveat.  Make sure that if you do this you don't have measures that are impossible to calculate based on an aggregate data set.

               

              For example, here is an aggregation of Average Sales by Category and Sub-category:

               

               

              If I'm working with the detail data set I can remove Sub-category and get a correct value for average.  But, if the above table was my aggregate data set (which is definitely smaller), then there is no way for me to get the average sales by category.  The "best" I could do is get an average of averages (which is wrong).  Technically, you could calculate the average yourself (using the sum of sales / number of records*), but you might come across some measures that you cannot calculate using an aggregate data set (especially things requiring distinct counts).

               

              In other words, be very aware of non-additive measures if you create an aggregate extract.

               

              Hope this helps!

              Joshua

               

               

              *the actual stored value for number of records in the original detail data set, not the 1 record per Sub-Category that will exist in the aggregate data set.

              • 4. Re: Filters while joining tables
                anuashka sharma

                Thank you all for your suggestions... but I am still getting the spool space error. Does hiding drop those columns from the table or it just does not show them in the output?

                • 5. Re: Filters while joining tables
                  Joshua Milligan

                  Is it possible that there's something going on with the join that causes it to blow out the number of rows -- some kind of Cartesian product?  How large are your original tables?

                   

                  -Joshua