4 Replies Latest reply: May 15, 2014 9:55 AM by Oscar _ RSS

    Calculated Fields

    Oscar _

      I have a list of credit card numbers that I want to use to create a new field.  The field I want to create would have cc numbers beginning with 4 return as CC-VM, beginning with 3 as CC-AM.  I have tried using the a lookup calculated field with no luck.  Can anyone help?

        • Calculated Fields
          Grant Perkins

          Hi Oscar and welcome.

           

          If the CC Number field is Character type

           

          left(ltrim(),1)

           

          Should give you the first character for your look up table. LTRIM may not be required but often is.

           

          If it is NUMERIC

           

          left(ltrim(str()),1)

           

          should give you the first character in a new Character type calculated field from which you can do the lookup.

           

          (Or of course you can simple use the appropriate formula when you define the lookup.)

           

          Given the lookup will be quite constant over time an intrnal lookup table would seem like the best solution

           

          An alternative is to use an IF function to calculate the field. For example;

           

          IF(left(ltrim(),1)="3","CC-AM",IF(left(ltrim(),1)="4","CC-VM", "Other")

           

          Should give CC-AM if the number starts with3, CC-VM if itr starts with 4 or "Other" if it is neither 3 nor 4. (Assuming I typed the formula right!)

           

          HTH.

           

           

          Grant

          • Calculated Fields
            Oscar _

            When I apply the formula it says "Invalid Delimeter"

            • Calculated Fields
              Grant Perkins

              Originally posted by Oscar:

              When I apply the formula it says "Invalid Delimeter" /b[/quote]Which formula?

               

               

              Edit: The IF based formula needs another ) on the end.

               

              IF(left(ltrim(),1)="3","CC-AM",IF(left(ltrim(),1)="4","CC-VM", "Other"))

               

              I assume that was the one you had a problem with?

               

              Grant

              • Calculated Fields
                Oscar _

                Thanks! It worked like a charm.