0 Replies Latest reply on Oct 19, 2012 9:07 AM by Richard Leeke

    Handy Tip: How to search within returned results

    Richard Leeke

      For some reason the forums decided to notify me about this idea - not sure why it picked that one in particular.

       

      Anyway - whilst I agree that a search feature within the returned result-set would be a nice feature, there is actually a very effective trick you can use to achieve the outcome already. I first came up with the idea when I was working from a 100 million row data extract. I had filters which would return a couple of thousand rows of interest within a few seconds, but I then wanted to search within some long text fields on those returned rows. Just adding another Tableau filter would cause Tableau to go back and run another query on the database. In this case, because it was trying to search on a long text field in a data extract it resulted in a query that took 5 minutes or so (extracts aren't good for that sort of search). That was really frustrating when it could get the 2,000 rows of interest in a few seconds, so I figured out a way to make it do further searching within the 2,000 rows, rather than going back to the database.

       

      The trick is to define a calculated field which just returns the value of the field that you want to search by using the LOOKUP() table calculation function, with an offset of zero - i.e. just look up the value in the current row. The table calculation forces any subsequent operations on that calculated field to happen in Tableau, without going back to the database. So you either add a filter on the field, or use an expression with your search criteria to highlight the relevant rows in some way. I often define a parameter that I use to enter a search term and a calculated field to highlight the matches, as in the attached Superstore Sales example. You can then keep entering different search terms with the parameter without ever re-querying the database.

       

      This trick brought the searches on my 2,000 rows out of 100 million down from 5 minutes to sub-second. This doesn't just apply to data extracts, it has exactly the same effect of avoiding re-querying the database with any data source.