8 Replies Latest reply on Jan 4, 2019 7:39 AM by Ken Flerlage

    REGEXP to be able filter by a comma-separated value in a Dimension?

    Martin Lisanik

      Hello,

       

      I have a dimmension, which contains rows with values separated by comma. What I would like to to is to be able to filter rows based on

      the comma separated values in the row.

       

      For example, I have a data looking like this:

       

       

      ID          Blocked          Labels

       

      15          yes               158,I-786,New Request,AZD78-A,RE-Program-Improvement

      85          no                 RE-Program-Improvement,AZD78-Z,Improvement, 786-Z,286

      786        N/A               New Request,869, AZD78-A,I-786

       

      I would like to create a filter with which I would be able to select only rows, which contain certain labe.

      I tried: IF CONTAINS and also a calculated field saying something like:

       

      IF REGEXP_MATCH([Labels],'I-786') THEN 'I-786'

      ELSEIF REGEXP_MATCH([Labels],'de-DE') THEN 'de-DE'

      ELSEIF REGEXP_MATCH([Labels],'AZD78-A') THEN 'AZD78-A'

      ELSEIF REGEXP_MATCH([Labels],'158') THEN '158'

      END

       

      However, this did not seem to work properly and it only showed 3 values max.

       

      Does anybody know how I could create a calculated fiel that would take any value separated by comma so I can use it as a filter?

       

      Thanks.

        • 1. Re: REGEXP to be able filter by a comma-separated value in a Dimension?
          Ken Flerlage

          The problem here is that your IF statement will only return a value for the first true criteria. So, for example, your first record contains "I-786", so the if statement will be true and it will return "I-786". But it also includes "AZD78-A". However, the IF statement won't continue onto that test, so it'll never return "AZD78-A". It will only return a single value. My guess is that you want to return multiple values. That's a bit trickier as it would require you to create new records for each label. If interested, there are some hacky ways to make this work. Let me know if you'd like to hear about them.

          • 2. Re: REGEXP to be able filter by a comma-separated value in a Dimension?
            Rodrigo Calloni

            Hi Martin

             

            You may regret about this later but you could use IF CONTAINS and test all your options.

             

            IF CONTAINS([Labels],'I-786') THEN 'I-786'

            ELSEIF CONTAINS([Labels],'de-DE') THEN 'de-DE'

            ELSEIF CONTAINS([Labels],'AZD78-A') THEN 'AZD78-A'

            ELSEIF CONTAINS([Labels],'158') THEN '158'

            END

             

            I say that you may regret because this kind of manipulations in Tableau can cause performance issues.

             

            Do you have access to Tableau Prep? If you do, you can do all these cleaning there and have a perfect dataset to use in Desktop later.

             

            I hope this helps

            Rodrigo

            • 3. Re: REGEXP to be able filter by a comma-separated value in a Dimension?
              Martin Lisanik

              Hello Ken,

               

              yes, that explains the behaviour I saw when I was using REGEXP. I would like to hear about the other hacky ways to deal with this issue.

               

              Thanks you for your time,

               

              Martin

              • 4. Re: REGEXP to be able filter by a comma-separated value in a Dimension?
                Martin Lisanik

                Hello Rodrigo,

                 

                at the end, that was the temporary solution I used. It works fine, however, my dataset is constantly changing and so I need to keep track

                of the changes all the time so I do not forget about a label. That´s why I hoped to come up with a somehow automatic way.

                 

                Yes, I do have Tableau Prep and I looked into that but I am not sure how to use it for this usecase.

                 

                Martin

                • 5. Re: REGEXP to be able filter by a comma-separated value in a Dimension?
                  Ken Flerlage

                  So, before we look at a hacky method in Tableau, as Rodrigo noted, you can do this in Tableau Prep as follows.

                   

                  1) Bring in your data.

                  2) Create a new clean step then make it an automatic split.

                  This will give you 10 fields splitting the label by commas. If you have more than 10, you can increase the number.

                  4) Create a pivot step and add all of the split columns you created previously.

                  When this is done, you'll have a data set that has each label in it's own record.

                   

                  See attached Tableau Prep flow.

                  1 of 1 people found this helpful
                  • 6. Re: REGEXP to be able filter by a comma-separated value in a Dimension?
                    Ken Flerlage

                    Now here's the hacky way to do it within Tableau, without first using Prep. Like the Prep method, our goal is to get each label in it's own row like this:

                     

                    IDBlockedFinal Label
                    15yes158
                    15yesI-786
                    15yesNew Request
                    15yesAZD78-A
                    15yesRE-Program-Improvement
                    85noRE-Program-Improvement
                    85noAZD78-Z
                    85noImprovement
                    85no786-Z
                    85no286
                    786NANew Request
                    786NA869
                    786NAAZD78-A
                    786NAI-786

                     

                    To do this in Tableau, we'll need to first create another data source, with a list of numbers. I created it in Excel.

                    This will allow us to do something similar to the 10-part split we did in Tableau Prep.

                     

                    In Tableau, join this new data source to your original data source using a 1=1 join calculation. This will essentially create 10 duplicates of your original data (one for each Label Number).

                    Now perform a split on Labels.

                    You should now have 10 new fields just like in Prep.

                    Next create a calculated field that will pick one of these 10 labels depending on the Label Number.

                     

                    Final Label

                    // Get the label based on the label number

                    CASE [Label Number]

                    WHEN 1 THEN [Labels - Split 1]

                    WHEN 2 THEN [Labels - Split 2]

                    WHEN 3 THEN [Labels - Split 3]

                    WHEN 4 THEN [Labels - Split 4]

                    WHEN 5 THEN [Labels - Split 5]

                    WHEN 6 THEN [Labels - Split 6]

                    WHEN 7 THEN [Labels - Split 7]

                    WHEN 8 THEN [Labels - Split 8]

                    WHEN 9 THEN [Labels - Split 9]

                    WHEN 10 THEN [Labels - Split 10]

                    END

                     

                    Now let's build a view and see what we have.

                    Okay, we're getting there, but we want to filter out the blank labels. Because we're going to use Final Label as a filter in the future, let's duplicate that field into one called Final Label (copy).

                     

                    Now let's build a view to show how you'd use it.

                    We'll filter out any blank labels.

                    Then we'll add Final Label as a filter to allow the user to select labels of interest.

                    Commas.gif

                    I'm attaching my sample data set and the Tableau workbook.

                     

                    Note: You will need to be careful with either of these methods as they will duplicate your data. That means that you could run into issue if you're performing any aggregations. If you do run into that, please feel free to open a new post on the forums and we'll help you out.

                     

                    If this resolves your problem, please be so kind as to mark one of my responses as helpful and/or the "correct answer". This will allow us to close this thread and will make it easier for others to find the solution to similar problems in the future. Thanks!

                    • 7. Re: REGEXP to be able filter by a comma-separated value in a Dimension?
                      Martin Lisanik

                      Hello Ken,

                       

                      I have just tried the option with Tableau prep and it seems to be working! Thank you very much!

                      I will indeed need to perform aggregations and various calculation wiith this data set so I might get back you again!

                       

                      Thanks a lot!

                       

                      Martin