7 Replies Latest reply on Jul 12, 2017 12:11 PM by TomHays

    Calculation question

    mjbenun

      This should be an easy one for many of you- but I'm not sure how to proceed.

       

      I have item numbers in my database that are 5-6 numbers followed by two letters- e.g. 685499DG.  I use a calculation to isolate the last 2 letters in a field called BrandCode.

       

      Problem is, we just introduced new item numbers that have only 1 letter at the end- eg. 685499R and for those items the Brand code is "R".

       

      I need a formula that basically looks for the letters at the end- wether it's 1 letter, 2 letters, etc.

       

      Thank you!

        • 1. Re: Calculation question
          philmodjunk

          If there are never letters anywhere but at the end and they are always upper case:

           

          Filter ( YourField ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )

          1 of 1 people found this helpful
          • 2. Re: Calculation question

            Filter ( Upper ( Field 11 ); "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ) 

             

             

            im sure there are better ways...

             

             

            *edit*  sorry phil   I guess I was responding while you posted.

            1 of 1 people found this helpful
            • 3. Re: Calculation question
              philmodjunk

              This is on the those "cats" that can be "skinned" any number of ways. Much depends on how many different ways the data might vary from record to record--which we can't see from a single sample.

              1 of 1 people found this helpful
              • 4. Re: Calculation question
                mjbenun

                Thanks for the reply..  they can be both upper or lower.  Is your calc case sensitive?

                 

                would I need to add all the lowercase letters as well?

                • 5. Re: Calculation question
                  TomHays

                  The following calculation will locate all non-numbers following the last number in the string for the field YourField.

                  If there are no numbers, it will return the entire string.

                   

                  Let([

                  theInput = YourField;

                  _n = GetAsNumber(theInput);

                  _last_digit = Right(_n; 1);

                  posn_last_digit = Position(theInput, _last_digit, Length(theInput),-1)

                  ];

                  Right(theInput; Length(theInput) - posn_last_digit )

                  )

                   

                   

                  685499DG --> DG

                  685499R --> R

                  AX32154M32XXX --> XXX

                  234rAb --> rAb

                  ABCD --> ABCD

                   

                   

                  -Tom

                  • 6. Re: Calculation question

                    the filter function is sensitive , but you can use the "upper" or "lower"  to control the format you want.

                     

                    i.e.   the original field  can be case insensitive, but your calculation to remove numbers can be set to either or

                    1 of 1 people found this helpful
                    • 7. Re: Calculation question
                      TomHays

                      On further study, I found that the behavior of GetAsNumber() was not quite suitable for this calculation.

                      My previous calc failed on "234.0dog".  It returned ".0dog" instead of "dog".  (GetAsNumber() doesn't recognize .0 as part of the number since it doesn't seem to like the trailing zero. It sees "456.1dog" well enough.)

                       

                      Here is a revised calculation using Filter() that works for all the previous input value formats and

                      "234.0dog" --> dog

                       

                      Let([

                      theInput = YourField;

                      _n = Filter(theInput; "0123456789");

                      _last_digit = Right(_n; 1);

                      posn_last_digit = Position(theInput; _last_digit; Length(theInput);-1)

                      ];

                      Right(theInput; Length(theInput) - posn_last_digit )

                      )

                       

                      -Tom