9 Replies Latest reply: May 15, 2014 10:02 AM by Grant Perkins RSS

    Strategies for extracting occasional information

    ltw _

      I continue to work on extracting information from a text file that is a series of several statements where each statement header is like:

      June 4, 2008                                           

       

       

      Rental Owner LLC                    UNIT 202 (ST202)

      John Doe                            SEASIDE TOWNHOMES               

      123 Main Street          

      Anytown CA 12345

       

       

                                             MONTH ENDING May 31, 2008[/CODE]

       

      and the detail is:

      EXPENSES:

         05/04/08 Complementary Maintenance WO 123456                      0.00

         05/01/08 Maintenance               WO 123457                     10.00[/CODE]

           Etc.

       

      For some statements, but not all, a line like:

      PLEASE REMIT $ 124.50 TO MAINTAIN YOUR ACCOUNT.[/CODE]

      will appear after the detail.

       

      Do capture this line, I have created a footer template that traps on "PLEASE RREMIT" and extract the data accordingly - no problem.

       

      The issue arises when there is no such footer line associated with a particular statement - that particular statement uses the next available footer line, which, of course, is not associated with the statement.  The result is that several statements may have the same remit amount, albeit incorrectly.

       

      So my question is, what are general strategies for dealing with this pheonmena?  Are there one-pass solutions or is this typically a two-pass solution?

       

      Thanks...

        • Strategies for extracting occasional information
          Data Kruncher

          The solution to which you refer involves what has lately been named a "guru trap"; one in which you can successfully append intermittent data (sometimes you have it, sometimes you don't) to the correct record.

           

          This "intermittent" data usually appears after the detail records, so a conventional append template doesn't solve this challenge. Nor does a footer template always provide a suitable solution.

           

          For your intermittent data, create an append template with exactly the same trap character(s) as were used in the detail template. Paint the field(s) required, and on the Advanced tab of the field properties dialog, set either of the "string ___ anywhere in previous line" or "preceding string ____ in current line" options.

           

          For your current example you could use the word remit for the current line option.

           

          Note that this approach appends the intermittent field to only the previous detail record. So while both expense items in your example are for the same Work Order number, the remit amount will only be appended to the one with the 10.00 amount.

           

          This probably isn't an issue though as you could easily summarize (and thus isolate) the remittance amounts by WO number.

           

          Search the forum for "guru trap"  - try searching tags - for other examples and discussions of such.

            • Strategies for extracting occasional information
              ltw _

              Thanks for telling me about the Guru Trap.  So I am using it on another set of data, for example:

              EXPENSES:

                 09/25/08 QWEST COMMUNICATIONS                                    41.72

                 09/30/08 OWNER HOUSEKEEPING CHARGES                             155.00

                             JONES 9/3-11/12                 

                 09/30/08 FOLIO ACTIVITY                                          71.83

                             PHONE CHARGES                     

                 09/30/08 EXCEL ENERGY (SEPT)                                     21.14

                 09/30/08 REPLACE KITCHEN INVENTORY WO 145264                      8.16

              /CODE

              You will notice that this has intermittent lines in the comments that appear after the items.  So with the detail lines as the date - description - cost, I set up an append Guru Trap on the detail lines with a field on a following line starting with a "preceding string in the current line" as the series of spaces that you see.  This indeed connects the note with the correct line.  It also connects it with the previous line if there is no note following the previous line.  So the "Jones 9/3-11/12" comment is attached to both the 9/25 item and the 9/30 item. 

               

              I tried using some formulas to separate them, but to no avail.  Any thoughts on this situation?

               

              Thanks...

                • Strategies for extracting occasional information
                  Data Kruncher

                  I didn't get that result. I used the first / in the date as the trap for the detail template, and the append template. I set the preceding string in the current line options with a bunch of spaces, and set the End Field On option to None of the above. This yields the simulated table view below:

                   

                  [FONT=Courier New]eg_09/25/08 eg_QWEST COMMUNICATIONS                 eg_41_72 eg_JONES 9/3-11/12[/FONT]

                  2008-09-25       QWEST COMMUNICATIONS                 41.72 /FONT

                  2008-09-30       OWNER HOUSEKEEPING CHARGES          155.00  JONES 9/3-11/12[/FONT]

                  2008-09-30       FOLIO ACTIVITY                       71.83  PHONE CHARGES[/FONT]

                  2008-09-30       EXCEL ENERGY (SEPT)                  21.14 /FONT

                  2008-09-30       REPLACE KITCHEN INVENTORY WO 145264   8.16[/FONT]

                  /code

                   

                  As a further test, I removed the "Jones" comment, and the result was that the "Phone charges" comment was only attached to the "Folio activity" record. Seems to be OK.

                   

                  Edit: the date formatting seems to have been altered by my copy/paste from Monarch to web browser.

                    • Strategies for extracting occasional information
                      ltw _

                      Thanks for the ideas.

                       

                      I created a new model and did exactly /Uas you said, but I don't get the same results.  For reference, I have Monarch Pro v9.01.

                       

                      The table result I get is:

                      2008-09-25 QWEST COMMUNICATIONS               41.72     JONES 9/3-11/12

                      2008-09-30 OWNER HOUSEKEEPING CHARGES          155     JONES 9/3-11/12

                      2008-09-30 FOLIO ACTIVITY               71.83     PHONE CHARGES

                      2008-09-30 EXCEL ENERGY (SEPT)               21.14     

                      2008-09-30 REPLACE KITCHEN INVENTORY WO 145264     8.16     

                      /CODE

                       

                      As you can see, the "JONES 9/3-11/12" comment appears with both the first and second records, rather than only the second record.

                       

                      For reference, relevant sections of the model information (Expansion of the .xmod file) appear below:

                           

                      /CODE

                       

                      What might I be doing different from you?

                      Thanks...

                        • Strategies for extracting occasional information
                          Grant Perkins

                          ltw,

                           

                          Your Detail and Append templates should both have only 1 line of sample data? (The number of lines is importrant, the actual data sample displayed is not in this case.) Paint the field for the Append in the sample line from where it starts for whatever width it can be and set up the 'Preceding string to the spaces as suggested by Kruncher.

                           

                          For the Guru Trap and Template to work the important thing is that the APPEND trap (or traps for multiple appends) has to be reset for ecah Detail record so any any APPEND template cannot have more sample lines defined that the MINIMUM number of lines found in the smallest DETAIL template. In this case that is 1 line.

                           

                          Hopefully that will get you where you need to be. But if not we can have another look at it.

                           

                           

                          Grant

                            • Strategies for extracting occasional information
                              ltw _

                              Thanks for the reply.

                               

                              If I understand correctly, the Guru Trap requires that the append templates have no more sample lines than the detail line has.  In my previous templates, the detail line has a single line of data, while the append template that extracts comments has two lines.  The difference in lines causes reset of the append template to take two lines, which causes it to connect to more than one detail record.

                               

                              I indeed observed this when I added additional lines to the text file: The comment for the "Owner Housekeeping" item was connected to that line and the one above it, but no others.

                               

                              So from your description, the alternatives are to make the detail template have two sample lines or make the append template have a single sample line. 

                               

                              I tried using two sample lines for the detail template, which actually tied the comment to only one line, but many of the lines were not captured at all.  Obviously, this is not a solution.

                               

                              I also tried making the append template a single line with the Preceding String argument as a series of 15 spaces as suggested by Kruncher.  Note that I had to include a trap of 15 blanks followed by a non-blank, which matches the Preceding String argument, for anything to be picked up.  This approach captures the comment line, but it is tied to the line following the line of interest and all following lines until another comment is encountered:

                              2008-09-25     QWEST COMMUNICATIONS               41.72     

                              2008-09-30     OWNER HOUSEKEEPING CHARGES          155     

                              2008-09-30     FOLIO ACTIVITY                    71.83     JONES 9/3-11/12

                              2008-09-30     EXCEL ENERGY (SEPT)               21.14     PHONE CHARGES

                              2008-09-30     REPLACE KITCHEN INVENTORY WO 145264     8.16     PHONE CHARGES

                              /CODE

                               

                              The thing about the two-line sample is that it appears to provide a method for Monarch to link the detail line and the comment line through the identical trap.  Is this what is really happening?

                               

                              Do you have other suggestions?  This would appear, to me anyway, to be a fairly common problem that a number of users would encounter.

                               

                              Thanks...

                                • Strategies for extracting occasional information
                                  Grant Perkins

                                  Your detail lines limit you to a single line sample, based the snippet of the report posted. Therefore the Append must also be a single line sample for this particular input report.

                                   

                                  A 2 line detail sample would mean Monarch would not see the second of any detail records that occur on consecutive lines - which would be half of them minus those records what have the extra description line.

                                   

                                  So, create a trap that gets your one line Detail and generate the fields from that line.

                                   

                                  Now use EXACTLY THE SAME TRAP AS YOU USED FOR THE DETAIL when you create your APPEND trap. You will be trapping on the same detail lines. Paint a field all the way across the line starting from the character position where any description that may exist would start. (Position 16?). The sample data displayed in the line at the time does not matter at all. Anything (or nothing) will do.

                                   

                                  Right click on the long field and in the 'advanced properties' set the 'Start field on' value to a preceding string to 15 spaces (if there are always at least 15 spaces before the descriptions). If there is never more than one line of extra description leave the 'End field on' set to 1 line. If there can be more than one line you will need to set one of the more variable rules - let's come back to that if we need to.

                                   

                                  Now Monarch should find all of your detail lines AND for each of them create an Append which will reset to a new value (possibly blank) for every detail record.

                                   

                                  The appends will read any lines between the Detail record to which they are attached and the next detail record and look for the 15 spaces that have been defined as the preceding string. If a line is not found the field will be empty. If found the field will be populated with the text from position 16 onwards. (Bear in mind that is you have other blank lines between two detail records in the report for any reason these MAY also appear)

                                   

                                  Warning. If you make your Append trap LONGER than the Detail trap (i.e. the last part of the trap extends to the right of the detail trap definition) you will not get the results you expect.

                                   

                                  If you make the trap shorter than the detail trap it should still work PROVIDED that nothing else might also be trapped in between the append and the detail. However for simplicity the easiest and most popular approach is usually just to make the trap (or even traps if you need multiple appends to work the same way) exactly the same as for the Detail record.

                                   

                                  Does this help at all?

                                   

                                  Grant

                                    • Strategies for extracting occasional information
                                      ltw _

                                      Okay, I guess I was dense.  After reading your reply, it became apparent that I should paint the comment field in the line above the comment where the trap is, which allows a one-line trap.  I originally thought that you were referring to the actual comment line.

                                       

                                      I did what you said, and it has indeed worked.  I think I can proceed from here.

                                      Thanks for all your help...

                                        • Strategies for extracting occasional information
                                          Grant Perkins

                                          This is one of those areas that can be, lets say, a little counter intuitive to start with. But once you have a feel for it and a couple of successes everything will fall into place and you will be able to a adapt the concept to many situations.

                                           

                                          There are logical constraints in terms of what you can do - how many lines you can include in your append is limited by the minimum number of lines in the smallest Detail record being one example.

                                           

                                          But there may be ways around the limit when required. Multiple appends would allow multiple lines for example, provided there was a way to uniquely identify the fields you need based on preceding strings.

                                           

                                          Think of Monarch mainly as a toolkit from which you can pick and use various tools and techniques in combination to achieve whatever it is you need to achieve. Sometimes the approach will be obvioous. At other times it will be less obvious. No mater. Experimentation is a great way to gain knowledge and experience. Hopefully your work time will allow you some flexibility in that regard. Be prepared to break some things that look like rules. Above all find a way to enjoy what you can do with Monarch - especailly when it appears to be outside the rules!

                                           

                                          HTH.

                                           

                                           

                                          Grant