7 Replies Latest reply: Jun 14, 2016 12:36 PM by hellback _ RSS

    Need a little help with a model Monarch 13

    hellback _

      I am trying to trap the second line in the following to get the name of the department.

       

      The problem is the Number may be anywhere from 5 to 8 digits long. This number is always the same as the First number on the Line with two sets of numbers (Line 4). I am hoping to do this with one trap. In the past, I have made multiple traps of varying lengths and then used a calculated field to get the "Real Department" name.

       

      Any help would be greatly appreciated.

        • Re: Need a little help with a model Monarch 13
          Olly Bond

          Hello hellback,

           

          From the example above, it's not easy to see the best approach. A floating trap might help - assuming that a) it's a text report and b) that the 5-8 numbers are starting in the left hand margin, and c) combined with a NOT trap on numeric to distinguish line 2 from line 4.

           

          From the sample, I guess the missing word you are hunting for is the "ADMIN" as everything else is repeated elsewhere. I'd be tempted to use a three line sample on the first template and grab the whole third line and attack it with extract(), substr() and rsplit(), but then you risk Page Header issues at page breaks.

           

          You also need to be certain that these appends always appear and aren't intermittently missed. If you can share the full (redacted) report, I'd happily take a look tomorrow. I presume you are using v13? I've got v10 through v13 here, so no problem if you need it in xmod, but let me know.

           

          Best wishes,

           

          Olly

            • Re: Need a little help with a model Monarch 13
              hellback _

              I am not sure how to upload a file so I can't give a sample without really big paste.

              The row I really don't need is Row 4 since it is the actual duplicate row. I was only trapping this to verify the department number.

              I need the whole name and number on Row 2. The Fund Name is not always included in the department name, especially for the General Fund.

              There is a lot of detail information with numbers, dates etc below this so a floating trap is not useful. And just my luck, some of the department numbers start with 600NNNN.

              What I have done in the past is to trap 5, 6, 7, & 8 digits separately with the name. I then use a calculated field to get the name basically to determine  if #Trapped = #from first part of line 4 = RealName. (Obviously this is not what my actual formula looks like.)

              I was just trying to see if there was an easier way to do this without creating multiple traps.

            • Re: Need a little help with a model Monarch 13
              Chris Porthouse

              Hello hellback,

               

              Can we get a little more detail about your report?  In the above example, are you trying to capture any line that begins with a number (of any length), followed by text in one template?  That means lines 1-3 are on one template, and line 4 is on another.  If this is the case, then a regular expression trap will do the job nicely:

              ^(?<CODE>[0-9]+)\s(?<DEPT>[A-Z ]+)

              The above trap will look for any line that begins with at least 1 number, place that value in a named reference called "CODE", followed by a single space, and then grab 1 or more upper case alpha character(s) or space(s) and place in another named reference called "DEPT".

              You could then use a standard or regular expression to capture the last line.

               

              Hope this helps.

              • Re: Need a little help with a model Monarch 13
                hellback _

                Probably should explain the rows

                The first row is the Fund, Second is the Department, Third  is the Account and fourth is the Department and Account numbers with the Account Name again.

                 

                These are Append rows with the details below.

                The details have lots of numbers as well as text (dates, PO numbers, Account Numbers, vendor information)

                  • Re: Need a little help with a model Monarch 13
                    Chris Porthouse

                    So if you only need the first 3 lines (in this sample), the regular expression trap above should work.  It currently grabs any number (of at least length of 1), followed by a space and then any number of alpha chacters and spaces as 1 field.  You can further refine the regular expression to say that you need at least 4 numbers if you find you are grabbing other data.

                  • Re: Need a little help with a model Monarch 13
                    hellback _

                    Thanks for all the suggestions. Unfortunately my data is not super uniform and I am not as well versed on expression traps as is probably needed. However you did steer me in the right direction. I could not get it to trap line 2 without also trapping line 3, (they are not always the same number of lines apart). I realized though that I could get the line 3 information from line 4. So I used an exclude trap for line 3, now I am getting only what I need.

                    Thanks for the ideas. I really appreciate the help.