10 Replies Latest reply: May 15, 2014 10:02 AM by dezideroo98 _ RSS

    dezideroo98's "Newbie" post

    Data Kruncher

      Originally posted by dezideroo98 in the KnowledgeBase and reposted in the regular Monarch forum:

       

      Greetings,

       

      I just started using Monarch Pro 7.0 this week and have to learn by trial and error for an important job. I have this file that I need your assistance in trappping it correctly. The file consists of several lines of data,sometimes you have 2 lines,3 or multiple lines. A sample of the file is listed below. I would appreciate if someone could help me. At the end of the fist line is the state abbreviation.The zip code is usually below it, depending if thereare any more lines of data. The zip code is 5 characters in length.

       

      Loan number  xxxxx   xxxx     xxxxx

                          xxxxx   xxx     xxx    xxxxxxxxx xxxxxx  State

      R       xxxxxxxxxxxxx   xxxxxxxxxxx                         55125

       

      Loan number  xxxxx   xxxx    xxxxx

                          xxxxx   xxx      xxxxxxxx        xxxxxx     State

                          xxxxx   xxxx     xxxxx                       12345

       

      Loan number  xxxxx   xxxx    xxxxx

                          xxxxx   xxx      xxxxxxxx        xxxxxx     State

                          xxxxx   xxxx     xxxxx                       12345

       

      Loan number  xxxxx   xxxx     xxxxx

                          xxxxx   xxx     xxx    xxxxxxxxx xxxxxx  State

      R       xxxxxxxxxxxxx   xxxxxxxxxxx                         55125

        • dezideroo98's "Newbie" post
          Grant Perkins

          Hi Dezideroo98 and welcome to the forum.

           

          I'm not entirely clear what you are asking for here.

           

          Is this some sort of address extraction?

           

          If so check out the help sections for Multi-Line (Or maybe it's Multi-row) fields and also the Address Block functionality. In combination they offer a comprehensive way to extract address lines that I could not improve on here even if I was able to!

           

          Near in mind that address data is often inconsistent. You may find that some records are less than optimal no matter what you do with them. Sadly that is the nature of name and address databases  - and maybe to some extent addresses that don't quite fit the standard rules!

           

          If it's not an address issue - sorry. Get back to us with what it really is and we will have another look.

           

           

          HTH.

           

           

           

          Grant

            • dezideroo98's "Newbie" post
              dezideroo98 _

              Hello,

               

              I did read some articles specially about multi-line trapping which seemed to be suitable for this type of format. However, I ran into some issues.

              1) When building a trap as Append, the line repeats itself till the next instance. In the example given,I was trapping the last line with letter R at the left column.However, the line repeats itself for line that did not have the letter R. As a result all lines have a R,when in reality only 30% have a letter of R at the end.

              2) Using the last line as a footer will also not be working, because the total number of lines vary from 2 to 8 lines.Not all last line have a letter R. SOme do not. But all have the last 5 digits which is the zip code.

               

              I feel the "guru trap" may be the solution,but have not yet being able to make it working. May be someone can explain in more details, I would appreciated very much.

                • dezideroo98's "Newbie" post
                  Grant Perkins

                  Hi,

                   

                  What is the significance of the R?

                   

                  If the entire block from the loan number line to the zip code line (possibly somewhere in the range 2 to 8 lines?) is part of an address then you need the multi-line processing methods and Address Block functionaliity rather than anything else.

                   

                  If that range of lines contains other information and therefore you need to restrict or constrain which lines are used THEN the 'guru trap' might have a purpose for you.

                   

                  The 'guru trap' works by setting a trap for an APPEND that is the same trap used by its associated detail record. In effect it limits the append to be an extension of the detail record. It also means that whereas an Append normally attaches data from lines BEFORE a detail record in this case it will always be taking fields from within the detail block or perhaps after it by allowing the field identification operation to 'float' vertically between the start of a record and the first line of the next occurrence of the trap. (See below).

                   

                  Most often for this to have any useful benefit the fields required need to be identifiable by some sort of preceding string - perhaps the name tag printed for the field that follows for example. It could be anything but it must be unique enough to select the field required.

                   

                  So using your sample 3 line data blocks. If you could only take the first 2 lines as the detail record (because some records only have 2 lines and there is a reason why the multi-line method would not be appropriate) then you could create an APPEND that starts at the same line but looks for a field in a line starting with the letter R. That field would then be populated (in the table) for records 1 and 4 but not for records 2 and 3.

                   

                  However if you are processing an address block, which seems likely given the zip code in the last line, then I would imagine you always need the last line whether or not it starts with an R. I think that is what you are saying, so the concept of a footer template does not seem to be appropriate.

                   

                  If you have the Monarch Learning Guide available there is, as I recall, a chapter in it that deals with address blocks. There certainly is in recent versions. I will guess that you don't have the learning guide in which case check the Help  - it should contin much the same useful information about how to work with address blocks. You should ignore the ZIP CODE trap if you are using it - it is an older and less flexible method of capturing address information.

                   

                  HTH.

                   

                   

                  Grant

                    • dezideroo98's "Newbie" post
                      dezideroo98 _

                      May be an example of the type of report I am facing with will help. The loan number is a 10 digit and separated by a blank. The state column is always aligned. The zip code will vary depending on the amount of line in between. It will always be there ,regardless of the letter R. The significance of the letter R is a flag indicating the type of tax and is most important. I tried to trap using either the footer trap or append, but the data repeats itself until the next R line. I could not get the "guru trap" to work,may be I did not do it right. Can someone help ? Here is a sample of the report I am dealing with.

                       

                      189xxxxxxx    987      01-35            1.00    06                                                                               

                      312      20   34049       Smith      John R                 1185    anystreet       NEW ALBANY            OH                                                                               

                      43054                                                                               

                      189xxxxxxx    987      11-35            1.00    12                                                                               

                      312      01   45153       Mendez     JOSE LUIS               3546    Main St        WOODBRIDGE            VA                                                                               

                      22193                                                                               

                      189xxxxxxx    987      03-09        3,540.60    12                                                                               

                      312      22   04073       Last         First                   971    Main ST       CHULA VISTA           CA   

                                              12-07        3,694.72    12                                                                               

                      319      21   04073                                                                               

                      R         18972280                         21  04073        54608                       5953213016         91915                       

                                                                 22  04073        54608                       5953213016   

                       

                      189xxxxxxx    987      11-08        2,483.99    12                                                                               

                      312      01   09011       SmithsonARET     5707 Any ST                              FORT LAUDERDALE       FL   

                      R         74973093                         41  09011        54608                   19112-24-15600         33319                       

                                                                 01  09011        54608                   19112-24-15600               

                       

                      189xxxxxxx    987      01-35             .00    12                                                                               

                      312      01   06001       Lilupith       ROBERT S       12    PEACEFUL DRIVE        NEW FAIRFIELD         CT                                                                               

                      06812                          

                                            /CODE

                        • dezideroo98's "Newbie" post
                          Grant Perkins

                          In the sample, where you have "R" lines, the data tothe right seems to shift across form the columns one might expect than to be displayed in. Is that an edit and post problem or dioes that happen in the real report?

                           

                          Apart from that the inconsistently floating ZIP code suggests that this is a report with some presentational problems in terms of its consistency so I am not surprised you are having problems with it.

                           

                          Also you seem to have a report with two different types of detail and a variable number of lines possible for one of them.

                           

                          What are you trying to extract from the report? What do you want to see as the output?

                           

                           

                          Grant

                            • dezideroo98's "Newbie" post
                              dezideroo98 _

                              Hi Grant

                              No it is supposed to be that way. And yes, I want to pull all the data including the zip code. What I ended up doing is creating several traps: Append, footer and detail. For footer, I trap on any line starting with a R including the zip code. For Append, I trap on any line with a loan number and the zip code. ANd finally on detail line, I trapped based on column, i.e., loan number, inv and due date and disb amount, to try to capture all possible lines. From then it is a manual process in Exel to clean the output.A very long and tedious process .  It is temp fix until I can have this model working.

                                • dezideroo98's "Newbie" post
                                  Grant Perkins

                                  You would likely be better off with a 2 or 3 stage Monarch extract and 're-build' process to achieve the result you currenly do in Excel.

                                   

                                  It is not a nice report to work with - partly due to that irregular zip code position and partly because you seem to have two lots of detail records with different purposes when the R lines are included.

                                   

                                  What does your final Excel record look like? Seeing that should provide a useful indication of what you need to get out of the report, also better if we have some way of naming to refer to the fields as captured and differentiate between the fields with similar values but a completely different purpose. Absent that things could get very confusing.

                                   

                                   

                                  Grant

                                    • dezideroo98's "Newbie" post
                                      dezideroo98 _

                                      Ok, how would I do this. As a start, refer to my screen.

                                       

                                      1st line:

                                      1st column: loan number

                                      2nd column: inv

                                      3rd column: due date

                                      4th column: disb amt

                                       

                                      2nd line:

                                      3rd column: DT

                                      4th column: sq

                                      5th column: payee

                                      6th-10th column: name and address

                                       

                                      3rd line (starting with R)

                                      1st column: R

                                      2nd column:contract

                                      3rd column: date

                                      4th

                                      5th

                                      6th : MTG ID

                                      7th: tax id

                                      8th: zip code

                                       

                                      I tried to separate the report but can not seem to get the guru trap to work properly. The line keeps on repeating itself. Can you help ?

                                        • dezideroo98's "Newbie" post
                                          Grant Perkins

                                          Does the report come with any header lines for each page or section that identify the fields that appear below?

                                           

                                          Most do, but not all, so it may not be that helpful.

                                           

                                          The variability of the report is a serious problem that makes the chances of a consistent result unlikely.

                                           

                                          Basically in each 2 line record (IGNORING THE R LINES FOR NOW) you have an Account/Name/Address records and a 2 line transaction (detail) record. But SOMETIMES you have a second detail record that shares the same Account/Name/Address information as the first detail record for that account.

                                           

                                          So the logical thing is that you have a 2 line DETAIL record and an APPEND template that captures the Account number, Name abnd address information. That would include the Zip code where the report treats and positions it logically - i.e. where there are no R records.

                                           

                                          The R records seem to be, in effect, another detail record with a link to the Account Number. Is there also a link between the R lines and one or more of the main detail transaction lines?

                                           

                                          If there is such a link then we could think of making the connection between the detail lines and the related R line(s). But if there isn't it is not clear how we should deal with these 'second detail' records on the basis of the information in the report. If bothe the detail lines AND the R lines need to be associated directly but independently to the account number/name/address/etc. then the only logical approach is to grab everything and attempt some conditional processing to slice and dice each data line.

                                           

                                          I think you will need to do that for the detached zip codes anyway but that alone is not too complicated.

                                           

                                          Assuming you have Monarch Pro the easiest route is likely to be to run 3 separate, simpler extractions to make the process as clean as possible.

                                           

                                          One to extract just the R lines with the Account number and whatever else is required for a lookup link to put the data back together later.

                                           

                                          Another to extract the 2 line detail records as we currently think of them.

                                           

                                          The third to extract the name and address and account number information. This will act as the MASTER file to which the other two extracts can be externally linked.

                                           

                                          Now, at this point we stop if you don't have the Pro version.

                                           

                                          If you do have Pro that's good but we need to check (because I can't remember right now) whether the ability to define multiple external lookups came with V7 or V8. If not until V8 then we again have a problem. At which point I would suggest upgrading to V10 if at all possible since the cost will likely be more than repaid with the development time saving of the upgraded facilities since V7.

                                           

                                          Edit: V7 Pro is OK for the external lookups - up to 9 are allowed.

                                           

                                          Once you have the three extracts it should be a relatively simple job to link everything back together how you need it to be and then deal with the zip code anomaly, where is occurs, using some conditional processing.

                                           

                                          Based on the sample layout I can foresee some other interesting challenges but I'm not sure yet if they are real format variations or editing anomalies introduced between the original report and the presentation in the forum.

                                           

                                          So, does any of that make any sense and if so do you feel comfortable with with creating the models for the 3 extractions?

                                           

                                           

                                           

                                          Grant

                          • dezideroo98's "Newbie" post
                            dezideroo98 _

                            Thanks Grant

                             

                            That is what I thought of last night. To have 3 separate reports and linked them thru the loan number as the point of reference. Thanks again for looking into this for me. I do appreciated it a  lot.