5 Replies Latest reply on Aug 31, 2018 7:09 AM by adrian.nedelcu

    Filter a concatenated column

    adrian.nedelcu

      Hi, I have an extract where one of the columns may have different strings with a delimiter in between.

      NameCityFavorite cars

      John

      Londonbmw; audi; volvo
      SteveBostonaudi; honda
      GeorgeDublinbmw; toyota; opel
      MaryParisaudi; kia; ford; lexus
      StacyMoscowbmw; ford
      LindaRomesubaru; opel; bmw
      BillSan Francisoford, bmw; toyota; honda

       

      I need to add a filter (hopefully using a calculated field) that can display the distinct possible values and have the option to filter for exmaple for the users that have "audi" or they have "toyota and ford" in the list.

       

      Do you have any idea how could I do this?

       

      Thank you

        • 1. Re: Filter a concatenated column
          John Sobczak

          You can always use the quick filter wildcard tab and then contains option on the Favorite cars field.

          • 2. Re: Filter a concatenated column
            suman kumar

            HI

             

            Use wildcard like below image in Tableau. drag and drop Favorite Car dimension in fillter and use below step.

            Screen Shot 2018-08-30 at 7.18.19 PM.png

             

            Best-

            Suman

            • 3. Re: Filter a concatenated column
              adrian.nedelcu

              the problem is that I need to display it as a multiple values list filer for the end user of the dashboard. Sorry I did not mentioned that from the start.

               

              • 4. Re: Filter a concatenated column
                Jonathan Drummey

                Hi Adrian,

                 

                This is a challenging problem for Tableau for two reasons:

                 

                1) The raw data has a multi-valued field. From a database standpoint (and Tableau works with data like a database) these are fairly terrible constructs because they break notions of one "cell" or "tuple" meaning one thing, instead that one entry has multiple meanings.

                 

                2) The way Tableau's categorical quick filters work is that for a given field they are an OR filter. So selecting BMW and Ford would return all Name/City combinations that have BMW, Ford, or BMW and Ford. From your description you're wanting an AND filter, so selecting BMW and Ford would only return customers with both.

                 

                The workarounds that I've used are:

                 

                a) Figure out the maximum number of simultaneous selections a user might want to make. Then build a parameter with the list of values, duplicate that parameter N-1 times, and build a calculated field for the filter that uses all of the parameters with a formula like CONTAINS([Favorite cars], [param1]) AND CONTAINS([Favorite cars], [param2]) and so on. This doesn't require any data prep but isn't a great user experience.

                 

                b) Split the field to multiple rows, for example the first original row with John/London would have rows for BMW, Audi, and Volvo. We can't do that operation in Tableau Desktop, you'd need to use Tableau Prep or some other tool like Alteryx or PowerQuery. Then write a set of calculations using LOD expressions to determine how many values selected, then use additional calculations to determine how many were selected for each person and then compare the two to figure out if they'd selected enough. Besides the extra work for data prep this can fail when the data is sparse.

                 

                c) I haven't used this one yet in production...v2018.3 (in beta) has a new feature called Set actions. With the split to Rows we can use a Set Action instead of a quick filter to get the results (I can share a demo workbook of this if you like). The issue with this is that we need an origin worksheet for the Set Action instead of the multi-select Quick Filter control and if the list of values is long it can be more challenging to work with.

                 

                Jonathan

                2 of 2 people found this helpful
                • 5. Re: Filter a concatenated column
                  adrian.nedelcu

                  Hi Jonathan,

                   

                  first I want to thank you for taking the time to write such a detailed answer. I will explore all the options and post an update here with the outcome.

                   

                  But thank you again, this will probably help others facing with a similar dilema.