3 Replies Latest reply: May 15, 2014 10:12 AM by psamson _ RSS

    Trapping the first lette of a column

    psamson _

      Hi

      I need to create a new column in my table with the first letter of a column.

      example

      #item            new column

      B500-1           B

      C569-2           C

       

      I want to be able to summaryse my item by categore who is represented by the first letter of the item.

       

      Thanks for your help

        • Trapping the first lette of a column
          Eamon Donoghue

          Hi psamson,

          You need to create a new Calculated Field called #item.  You can do this using the following sequence:

           

          In the Report window click on the Calculated Fields icon

          Select New to create a new Calculated Field (Column)

          Select Formula Based and click OK

           

          You will then see the Field Properties Dialog.

          On the General tab enter a Name and Type for your new Field. 

          In this case you should enter "new column" (without quotes) as the Name and select Character as Type from the drop-down list.

          Next, click on the Formula tab and enter the following expression (again without quotes)

           

          Left(#item,1)

           

          This should give you a new column containing only the leftmost character for the #item Field.

          You can easily create a Summary using #item as the Key field and this will give the required result.

           

           

          If you have any further questions, please feel free to post here or PM me any time.

           

           

          hth.

           

          Best regards,

          Eamon.

            • Trapping the first lette of a column
              Eamon Donoghue

              Hi again psamson,

               

              Just a thought - you'd probably be better off using an Alphabetic character at the start of your field names ( #item).  If your data is exported to a database, you may run into trouble with field naming rules.  I would make the same comment about the use of the Space character (new column).  I'd recommend an Underscore character if necessary or, better still try CamelCase  - you might need to Google this one  

               

              Hope you don't think I'm being a pedantic pita here, but like most people around here, I've made the same mistakes myself!

               

              Best of luck with your model building. 

               

              Best regards,

              Eamon.

              • Trapping the first lette of a column
                psamson _

                Thansk for your help Eamon