7 Replies Latest reply on May 8, 2012 1:57 PM by TomHays

    Calculation to filter out words after ":" or "-"

    m.bodily.consulting

      Hello,

      I am an amateur when it comes to formulating calcualtions. Mostly through trial and error do I get things right. So I was wondering if someone knows how to do this faster than I do. Here is what I'm trying to do:

      I need a calculation to search in the Title field for ":" and "-" and if the field has either character I need all words that follow it to be excluded.

      Example: (Title field) William Teller: A short story >> (calculation output) William Teller

        • 1. Re: Calculation to filter out words after ":" or "-"
          TomHays

          Try this one.

          -Tom

           

           

          Let(

          [

          theString = Title;

          thePos1 = Position(theString; ":"; 1; 1);

          thePos2 = Position(theString; "-"; 1; 1)

          ];

           

          Case(

          (thePos1 = 0) and (thePos2 = 0); theString;

          (thePos1 = 0); Left(theString; thePos2 - 1);

          (thePos2 = 0); Left(theString; thePos1 - 1);

          thePos1 < thePos2; Left(theString; thePos1 - 1);

          Left(theString; thePos2 - 1)

          )

          )

          • 2. Re: Calculation to filter out words after ":" or "-"
            dgail

            Try this.

             

            Case (

            PatternCount (Title ; ":" ) > 0;

                   Left (Title ; Length (Title ) - Position (Title ; ":" ; 1 ; 1 ) -1 );

             

            PatternCount (Title ; "-" ) > 0;

                    Left (Title ; Length (Title ) - Position (Title ; "-" ; 1 ; 1 ) -1 );

            )

            • 3. Re: Calculation to filter out words after ":" or "-"
              m.bodily.consulting

              Thanks Tom! This is just what I was looking for. I can see that my time would've been wasted because I don't think I would ever come up with that on my own.

              Is the spacing in your function a standard format for complex calculations?

               

              I wonder if you could help me with another calculation:

              If I wanted to search my Vendor field for entries that contain a ";" can I transfer all words before this character to Vendor1 field and all words after this character to Vendor2 field?

              Example: (Vendor field) Amazon; City Book Store >> (Vendor1) Amazon AND (Vendor2) City Book Store

              • 4. Re: Calculation to filter out words after ":" or "-"
                TomHays

                m.bodily.consulting wrote:

                 

                Is the spacing in your function a standard format for complex calculations?

                 

                I don't know that the format I used is a standard. I just like to format my calcs to make it readable and easy to edit.

                 

                Note that the calculation is a bit elaborate to accommodate strings that have both ":" and "-". When they are both present it separates the string at the earlier one.

                 

                -Tom

                • 5. Re: Calculation to filter out words after ":" or "-"
                  TomHays

                  m.bodily.consulting wrote:

                   

                  If I wanted to search my Vendor field for entries that contain a ";" can I transfer all words before this character to Vendor1 field and all words after this character to Vendor2 field?

                  Example: (Vendor field) Amazon; City Book Store >> (Vendor1) Amazon AND (Vendor2) City Book Store

                   

                  You would need two calculations. One for the Vendor1 field and another for the Vendor2 field.

                   

                  Vendor1 =

                  Let(

                  [

                  theString = Vendor;

                  thePos = Position(theString; ";"; 1; 1)

                  ];

                  Case(

                  thePos;Left(theString; thePos - 1);

                  theString

                  )

                  )

                   

                  Vendor2 =

                  Let(

                  [

                  theString = Vendor;

                  thePos = Position(theString; ";"; 1; 1)

                  ];

                  Case(

                  thePos;Right(theString; Length(theString) - thePos);

                  ""

                  )

                  )

                   

                   

                  If you desired, you could simplify the second calculation by using the already-calculated value of Vendor1, but I prefer to keep them independent so that you can calculate them in either order.

                   

                  -Tom

                  • 6. Re: Calculation to filter out words after ":" or "-"
                    m.bodily.consulting

                    I did notice the priority of stings. However, looking through more of my records I noticed that identifying the "-" character has made my titles appear incomplete. How do I delete the string parts that apply to the "-". I tried to do this on my own and I did it wrong because sometimes a number or nothing appears instead of text.

                    • 7. Re: Calculation to filter out words after ":" or "-"
                      TomHays

                      m.bodily.consulting wrote:

                       

                      How do I delete the string parts that apply to the "-".

                       

                      I think you are asking for a calc that only considers ":". This would essentially be the same as the calc you asked for that handles ";".

                       

                      Let(

                      [

                      theString = Title;

                      thePos = Position(theString; ":"; 1; 1)

                      ];

                      Case(

                      thePos;Left(theString; thePos - 1);

                      theString

                      )

                      )