3 Replies Latest reply: May 15, 2014 10:08 AM by Data Kruncher RSS

    Monarch Pro 9 question

    chickenjoy _

      How do you "move" certain data from a Calculated Memo Field into another Calculated Memo Field?

       

      Example: See Field "CalculatedField4". In this field, any string ending with BH is to be "moved" to a new column (new calculated field). Please see my desired output.

       

      http://i225.photobucket.com/albums/dd38/peytimuti/image.gif[/url]

       

      Any inputs is greatly appreciated.

       

      Thanks,

      Chickenjoy

        • Monarch Pro 9 question
          Data Kruncher

          Provided that CalculatedField4 has a fixed number of string segments within the string, in this case five, then this formula isolates the text ending in "BH":

          Trim(

          If(Right(LSplit(,5," ",1),2)="BH",LSplit(,5," ",1)+" ","") +

          IF(Right(LSplit(,5," ",2),2)="BH",LSplit(,5," ",2)+" ","") +

          IF(Right(LSplit(,5," ",3),2)="BH",LSplit(,5," ",3)+" ","") +

          IF(Right(LSplit(,5," ",4),2)="BH",LSplit(,5," ",4)+" ","") +

          IF(Right(LSplit(,5," ",5),2)="BH",LSplit(,5," ",5),"")

          )[/code]Reversing the logic, this will effectively strip the "BH" text from the original text:

          Trim(

          If(Right(LSplit(,5," ",1),2)="BH","",LSplit(,5," ",1)+" ") +

          IF(Right(LSplit(,5," ",2),2)="BH","",LSplit(,5," ",2)+" ") +

          IF(Right(LSplit(,5," ",3),2)="BH","",LSplit(,5," ",3)+" ") +

          IF(Right(LSplit(,5," ",4),2)="BH","",LSplit(,5," ",4)+" ") +

          IF(Right(LSplit(,5," ",5),2)="BH","",LSplit(,5," ",5))

          )[/code]

            • Monarch Pro 9 question
              Olly Bond

              Hello Chickenjoy,

               

              If you have a variable or multiple number of spaces between entries, you'll need to use Intrim() before the lsplit will work nicely.

               

              Also, for a variable number of entries on each line, you would have to know the maximum number of segments in advance of building the formula, or have an upper limit and build to that.

               

              Breaking the data out using a multi-column region and then creating a summary could give you an automated approach but it would likely require a two pass solution.

               

              Best wishes,

               

              Olly

               

              PS Chickenjoy, I notice that some of your posts refer to Monarch v9, some to Monarch v10? Why not upgrade the v9 users to v10, so that they could use the Column() function to solve problems like this?

                • Monarch Pro 9 question
                  Data Kruncher

                  Indeed, the only "cost" of building extra capacity with the given formula would be a little extra calculation overhead. The series could be extended to handle, say 100 segments, with only a little more copy/paste/revise work.

                   

                  The split functions are very forgiving if you specify more segments in the function than actually exist in the data.