12 Replies Latest reply on May 5, 2016 10:25 AM by pooja.gandhi

    Filter by substring

    K P

      Hello all,

      My data looks like this:

       

      Department Column

      Department of Biology

      Departments of Biology

      Division of Biology

      Biology Department

      Biology Center

       

      All I need is a string parameter where I input "Biology" and list look like this:

      Department of Biology

      Departments of Biology

      Biology Department

       

      I am struggling to write the formula that can give me desired result.

      Thank you.

        • 1. Re: Filter by substring
          pooja.gandhi

          Kush!

           

          What is the criteria for only 3 departments to show? I see all departments have the text 'Biology'? Is there another qualifier you did not mention here?

          • 2. Re: Filter by substring
            Andrew Watson

            Do you want to have only DEPARTMENT values to appear and then filter that down further when inputting Biology?

             

            Will the user be the one to input the word Biology (or other words for other depts.)?

            • 3. Re: Filter by substring
              K P

              Pooja,

              Basically, I need to keep values that contain

              Department of "Parameter String" or

              Departments of "Parameter String" or

              "parameter String" Department.

               

              if Value contains "parameter string" any other way, I don't need to see it.

              For Example, I don't need 'Division of Biology' or 'Biology Center'

              • 4. Re: Filter by substring
                K P

                Shawn,

                It will also give me values that contains for example " Yale university department of human Biology' .. Correct?

                I would actually like to filter it out.

                 

                I need something like this

                 

                CONTAINS([Department Column], 'Department of "PARAMETER"') OR CONTAINS([Department Column], 'Departments of "PARAMETER"') OR CONTAINS([Department Column],  "PARAMETER" Department')..

                ofcource formula above is not valid.

                • 5. Re: Filter by substring
                  K P

                  andrew,

                  yes, user will input parameter value and will only like to see values that contain

                  Department of "Parameter String" or

                  Departments of "Parameter String" or

                  "parameter String" Department.

                  • 6. Re: Filter by substring
                    K P

                    I can create parameter and user can input "Department of Biology"

                    and then they do another search for "Departments of Biology"

                    and then one more search with string "Biology Department"

                     

                    But, Its not Practical.

                    I want to give them list of all these 3 searches combined with just an input of "Biology"

                    • 7. Re: Filter by substring
                      pooja.gandhi

                      How about creating a calculation to see if the string contains the text 'department'? That takes care of department and departments.

                       

                      So, contains([Department Column], 'Department') drag it to filter and select true.

                       

                      Then secondary calculation to pick up the parameter value:

                       

                      if contains([Department Column], [Enter Text]) then [Department Column] end

                       

                      Finally keep only the 2nd calc on the view. I added Department of Mathematics to demo.

                       

                      • 8. Re: Filter by substring
                        pooja.gandhi

                        Oh didn't read your response to Shawn! Let me think some more.

                        • 9. Re: Filter by substring
                          K P

                          Yes thanks.

                          Yeah that's a problem.

                          We have list of hundreds of thousands of departments. So I need to match the sting exactly like "department(S) of string."

                           

                          I appreciate your time to help.

                          Thanks

                          • 10. Re: Filter by substring
                            pooja.gandhi

                            Ok how about changing calc 1 to this?

                             

                            if STARTSWITH([Department Column], 'Department') or STARTSWITH([Department Column], 'Departments')

                            or

                            ENDSWITH([Department Column], 'Department') then [Department Column] end

                             

                            Filter nulls out.

                             

                            • 11. Re: Filter by substring
                              K P

                              Pooja,

                               

                              Thank you.

                              This calculations is the best so far.

                              It still doesnt pick some values but it's data problem. For example they have some values like " Department of Biology, Princeton,NJ""

                              But I can make data team fix that and give me desired format.

                               

                              This is very helpful.

                              Thank you.

                              • 12. Re: Filter by substring
                                pooja.gandhi

                                Department of Biology, Princeton,NJ starts with 'department' so I am assuming the calc 1 filter should respect that. Worst scenario you could use some REG_EXP.

                                1 of 1 people found this helpful