1 2 Previous Next 15 Replies Latest reply on Oct 2, 2018 6:14 PM by MV V

    If with contains condition

    MV V

      Hi All,

       

      I have data like below.

        

      OderNumberName
      1S1 A1 C1
      2A1 C1
      3C1 S1
      4S1

       

       

      Requirement is to create a filter which has three values:

      A1

      C1

      S1

       

      Once user

           Select A1 all the OrderNumber which has A1 in the Names will be the output

      1

      2

           Select C1 all the OrderNumber which has C1 in the Names will be the outputs

      1

      2

      3

      Select S1 all the OrderNumber which has S1 in the Names will be the outputs

      1

      3

      4

       

       

      Creating a Calculated field so that i can use that as a filter

      IF

      CONTAINS([Name], 'A1') THEN 'A1'

      ELSEIF

      CONTAINS([Name], 'C1') THEN 'C1'

      ELSEIF

      CONTAINS([Name], 'S1') THEN 'S1'

       

      END

       

      Problem with the above approach:

      If user has selected C1

      1 and 2 will not be shown as it is in the first condition

       

      Regards,

      Manish Virmani

        • 1. Re: If with contains condition
          Okechukwu Ossai

          Another approach will be to use parameters like below.

           

           

          Hope this helps.

          Ossai

          • 2. Re: If with contains condition
            meenu choudhary

            Hi MV,

             

            You can try below steps:

             

            1. Create parameter Select Name with values :A1,C1,S1

             

            2. Create Variable  Flag :

             

            CONTAINS([Name], [Select Name])

             

            Drag this to filter and select "True".

            1 of 1 people found this helpful
            • 3. Re: If with contains condition
              MV V

              Thank you Okechukwu Ossai for your reply, but Cant use Parameter as User can select multiple values (sorry forgot to mention this).

              So it would be a Multiple value Select drop down.

               

              Thanks

              Manish Virmani

              • 4. Re: If with contains condition
                MV V

                meenu choudhary Thanks for your reply.

                But i have to show multiple value drop down. User can select A1 and C1 both (values will count twice but its business logic).

                 

                Thanks

                Manish Virmani

                • 5. Re: If with contains condition
                  Okechukwu Ossai

                  Hi MV,

                   

                  You need a cross join to achieve that. This will increase the size of your data. So, you need to carefully choose between the cross-join and parameter approaches.

                   

                  Step 1: Create another data source with the list of all department names. I did this in Excel in a tab called Dept

                   

                  Step 2: Join this to your main dataset (Sheet1) using a join calculation 1 = 1. This will duplicate the entire dataset for each department.

                   

                  Step 3: Create calculated field [Dept Filter] as shown below. Put [Dept Filter] on the filter shelf and set to True.

                   

                   

                   

                  Hope this helps.

                  Ossai

                  2 of 2 people found this helpful
                  • 6. Re: If with contains condition
                    MV V

                    Thank you for your suggestion Okechukwu Ossai. I will keep this as my last option.

                    I am reading about dynamic sets. Not sure if that will help me. I am still exploring the best solution. Will give you an update once i have anything better. Thanks!!!

                     

                    Regards,

                    MV

                    • 7. Re: If with contains condition
                      Rob Palmer

                      Hello,

                       

                      Do you have a limit on the number of values that can be included in the Name field? One other option is to split the field using the space and then pivot it so that your data looks something like:

                       

                      OrderNumber     Name

                      1                         A1

                      1                         S1

                      1                         C1

                      2                         A1

                      2                         C1

                       

                      That way you'll be able to achieve what you are after by using a filter on the name field.

                       

                      Cheers

                      Rob

                      • 8. Re: If with contains condition
                        MV V

                        Thank you Rob Palmer..But doing so again is not easy,, if i understood it correctly by doing this i needs to handle other Measures also as they will show multiple times.

                         

                        Deepak Rai Tushar  More  please help.

                         

                        Regards,

                        MV

                        • 9. Re: If with contains condition
                          MV V

                          No luck till now.

                           

                          A request to all the Data Monarch please help and share your inputs how to solve that in Tableau. (not in the back-end data / database).

                          Need a multiple value selector filter, not a Parameter in the above problem.

                          Jonathan Drummey Shawn Wallwork , I strongly believe you guys will help and provide a solution for above problem.

                           

                          Regards,

                          MV

                          • 10. Re: If with contains condition
                            Yuriy Fal

                            Hi MV,

                             

                            The suggestion by Rob Palmer is a solid one.

                             

                            You could prepare a distinct datasource (after Split & Pivot)

                            just for this view / analysis only.

                             

                            Any other views could be using your plain old datasource.

                             

                            Yours,

                            Yuri

                            • 11. Re: If with contains condition
                              Shinichiro Murakami

                              Hi All,

                               

                              Unfortunately, split and pivot is not available at one time.

                              So instead, duplicate data source with union and merge the dept name.

                               

                               

                               

                               

                               

                               

                               

                               

                              Thanks,

                              Shin

                              1 of 1 people found this helpful
                              • 12. Re: If with contains condition
                                Jonathan Drummey

                                Hi MV,

                                 

                                Shin & Yuri & Okechukwu are all really knowledgeable, I can't add anything to their replies.

                                 

                                Fundamentally Tableau works with data like a database does and Tableau likes "tidy data" where there is one attribute per column. Multi-valued cells as described here break that convention, so some sort of effort is necessary to work with them as if they are a single column, whether that is using multiple parameters or preparation of the Tableau data source or even further in the backend.

                                 

                                Jonathan

                                • 13. Re: If with contains condition
                                  MV V

                                  Thank you Shinichiro Murakami for detailed explanation of the Solution.

                                  Thank you Jonathan Drummey for sharing the logic. I totally agree with you and have great respect for all the people who helped me and gave the solution.

                                   

                                  Not sure how and why my colleague said it can be done with multiple dynamic sets. I will check with him on Monday.

                                   

                                  Regards,

                                  MV

                                  • 14. Re: If with contains condition
                                    Jonathan Drummey

                                    Hi MV,

                                     

                                    I'm interested in what your colleague is thinking of. I'm not sure what your colleague means by "dynamic sets", that term isn't familiar to me. Tableau presently has four types of sets that I'm aware of:

                                     

                                    - manual or constant sets that we create by selecting marks in a view and choosing to add them to a set

                                    - computed sets that are created by right-clicking on a dimension

                                    - combined sets that are created by combining manual and/or computed sets

                                    - user filter sets that are created using the Server->User Filter menu

                                     

                                    Tableau v2018.3 adds a new Set Action for views where we can update sets by selecting marks.

                                     

                                    In any case a Set is returning a boolean In/Out for each mark showing it is in the set or not. So to get all of the different combinations for 3 variables (S1, A1, C1) in any order with between 1 and 3 variables selected would require six sets, four different variables would require 14 sets, and so on. You could build a parameter to test for each of the combinations but that's not a multi-select parameter, that's a single select for the desired combination and the number of sets could quickly get very unwieldy.

                                     

                                    Jonathan

                                    1 2 Previous Next