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.
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.
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.
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.
312 20 34049 Smith John R 1185 anystreet NEW ALBANY OH
189xxxxxxx 987 11-35 1.00 12
312 01 45153 Mendez JOSE LUIS 3546 Main St WOODBRIDGE VA
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
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?
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.
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.
Ok, how would I do this. As a start, refer to my screen.
1st column: loan number
2nd column: inv
3rd column: due date
4th column: disb amt
3rd column: DT
4th column: sq
5th column: payee
6th-10th column: name and address
3rd line (starting with R)
1st column: R
3rd column: date
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 ?
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.
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?