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

    help with formatting

    dezideroo98 _

      Hello

       

      I have a report with the following format:

       

      Loan #

       

      Field 1A        Field 2A        Field 3A

      Field 1B        Field 2B        Field 3B

      Field NN        Field NN       Field NN

       

      The report has a 3 column report where all field information are properly aligned. The desired report, I need to pull up the following information:

       

      Loan #    Field 1B    Field 5A     Field 2C      Field 3E.

       

      The problem is that the current report I get is:

       

      Loan #                  Field 5A

      Loan #    Field 1B

      Loan #                                                      

      Loan #                                 Field 2C

      Loan #                  Field 3E.

       

      Can anyone help me ?

        • help with formatting
          Grant Perkins

          If you have a 3 column report and the columns are number 1 to 3 in your example, where does 5A come from?

           

           

          Grant

            • help with formatting
              dezideroo98 _

              fat finger issue ??:D

                • help with formatting
                  Grant Perkins

                  So what is the 5 supposed to be?  2 perhaps?

                   

                  The MCR function (I assume that is what you are using) runs down each column on a page and then move on to the next page and does the same

                   

                  So the unsorted table that would be produced would give you:

                   

                  1A

                  .

                  .

                  .

                  1n

                  2A

                  .

                  .

                  .

                  2n

                  3A

                  .

                  .

                  .

                  3n

                   

                  and then go to the next page and do the same again.

                   

                  If you need to replicate an ACROSS processing order (which is I assume what you want) then you need to sort the extractions in some way - ideally something to the left of the columnar definition will be available as a description of what the data in the columns represent. That should also prove useful for sorting and grouping.

                   

                  That said I seem to remember that under some circumstances using V7 there could be anomalies in the success of the extraction so be sure to double check what you get to make sure it is everything that you need.

                   

                  HTH.

                   

                   

                  Grant

                    • help with formatting
                      dezideroo98 _

                      Hi Grant

                       

                      Thanks for the response. The report I am trying to work with has 3 or more columns. For the sake of this example,I have 3 columns. The data I want are in all 3 columns. So the report I got is:

                       

                      *-----loan #1 -


                      field 1A--


                      field 3D

                      *-----loan #1


                      field 2C--


                      *-----loan #1--field 1D-field 2F--


                      .

                       

                      The report format I am looking for is:

                      Loan #1--field 1A, field 1D, field 2C, field 2F, field 3D.

                      Loan #2--field 1A, field 1D, field 2C, field 2F, field 3D

                      Loan #3--field 1A, field 1D, field 2C, field 2F, field 3D

                       

                      Hope this makes sense.

                        • help with formatting
                          Grant Perkins

                          So you want all the loans to pick out all the filled fields in all the columns?

                           

                          Are the Data numeric or alphanumeric? Do the columns have any usable headings? Are the columns consistent widths?

                           

                           

                           

                          Unless anything odd turns up I think my approach would be to extract as an MCR then sort the result by loan # column heading (in a specified order) and RowNo() (add this calculated field to the table.)

                           

                          Then, if the data are numeric you could then produce a summary with the column heading value as a key field set to display ACROSS rather than down and use the extracts data field as a MEASURE.

                           

                          If they are not numeric then export the table to a report in its new 'single column' form and then run a new instance of Monarch that models the new report. If you export the 'table' using a SUMMARY that looks just like the table you can suppress repetiton of the repeating fields - "loan #" for example - to give a nice ready Append field that will allow for a single row output from the table by a couple of different methods depending on what you need.

                           

                          Whilst there are a number of way to approach this sort of requirement I really don't yet feel I know the details of you data well enough to make a strong recommendation.

                           

                          HTH.

                           

                           

                          Grant

                           

                          PS. If the data is really quite messy this http://www.monarchforums.com/showthread.php?t=2510#article3[/URL]

                           

                          might provide some ideas that could be adapted. It is not directly comparable to your problem as far as I can tell but parts of the solution may be helpful to you.

                            • help with formatting
                              dezideroo98 _

                              Hello, sorry for the delay. I was trying to work on this issue alone,but am struggling to find an elegant way of doing this. This is the type of report I am working with. The data I want to obtain resided on different areas of the report, so using the MCR workout may not be efficient. Can anyone offer any suggestion.? I have truncated the report,else it can be 9 to 10 pages long.

                               

                              T1231-309                                            ABC residential listing                                                10/17/08

                                                                          MASTER RECORD STATUS REPORT, REQUESTED BY ABCdhe2222                          PAGE    7

                                CLIENT-NO            874  COUNTY-TAX           .00  PEND-PMT-CHG              1-BASIS-CODE         1XX  PRIME-RT-CD

                                LOAN-NO       0000000000  CITY-TAX             .00  P-COUNTY                  1-BASE-RATE               BILLING-DAYS

                                RECID,LI-FLG        A     HAZ-PREM             .00  P-CITY                    1-IR-CHG-PD            1  NO-OF-UNITS            1

                                INV,CAT         C01  004  MIP-PMI              .00  P-HAZ                     1-PI-CHG-PD           12  P-PLAN-CODE

                                INV-LOAN-NO   xxxxxxxxx   LIEN                 .00  P-MIP-PMI                 1IR-CHG-DATE    08-01-08  DISC-COLL-CL         .00

                                TYPE-LOAN             13  OV-SH-SPREAD              P-LIEN                    1-NEW-IRATE     .0912500  ACCR-STOP-DA

                                ST,CNY,CTY   04 065 0000  ESCROW-PMT           .00  P-OV-SH                   1PI-CHG-DATE    02-01-09  ACCR-STATUS            3

                                OFFICE                00  BSC-AMT              .00  P-A&H,-CD                 1ST-NEW-PI           .00  DISC-AMORT

                                MAN,TYPE-ACQ        0  1  235-FEE              .00  P-LIFE,-CD                1-ORIG-IRATE    .0275000  ACQ-DATE

                                1ST-PRIN-BAL   586174.69  HUD-PART             .00  P-MISC,-CD                1ST-ORIG-PI      1907.69  FORCED-COV

                                ESCROW-BAL           .00  MISC-AMT             .00  STOP-DATE                 1-ORIG-LTERM         480  FORCED-DATE

                                ESC-ADV-BAL          .62  REP-RES,TP        .00  0  P-HUD-PART                2-BASIS-CODE              BANKRUPT-CD

                                SUSPENSE-BAL         .00  DELQ-TABLE  000001011111  P-HUD-DATE                2-BASE-RATE               BANKRUPT-DT

                                DEFICIT-BAL          .00  LAST-LATE          10-08  FACTOR-DATE               2-IR-CHG-PD               MI-DELQ-STAT

                                LATE-CHG-BAL      492.20  BAD-CK-TBL  222224222232  PEND-P&I                  2-PI-CHG-PD               DISC-REM-TRM

                                REP-RES-BAL          .00  LAST-BAD-CK        00-00  PEND-INT                  2IR-CHG-DATE              SUBSIDY-IND

                                RES-ESCROW           .00  L-C-RATE             .00  P-INV,-CAT                2-NEW-IRATE               1098-EXEMPT

                                INT-DUE              .00  DEF-SCHED          00-00  P-INV-LOAN-#              2PI-CHG-DATE              PLEDGD-LN-CD

                                DISC-BAL             .00  LAST-ANAL          00-00  P-MORT-DATE               2ND-NEW-PI                PROPERTY-TP            1

                                HUD-PART-BAL         .00  F&EC-PREM            .00  PEND-ANN-S-F              2-ORIG-IRATE              LOAN-PURPOSE           6

                                ABBR-NAME    K  xxxxxx    DEF-INCREASE         .00  PEND-LEV-S-F              2ND-ORIG-PI               OWNERSHIP-TP           1

                                CO-MTGR-ABBR              1ST-PRIN-BEG   571969.69  P-BILL-MODE               2-ORIG-LTERM              DEVELOPMENT            1

                                 

                               

                                LINE-1  jones#smith                     TAX-NAME  jones           smith                P-TAX-NAME

                                LINE-2                                  NEW-PROP                                       SEC-LIENHOLDER

                                LINE-3                                  ADDR-ZIP  CORONA                CA 92883

                                LINE-4                                  SUBDIV.                                        ORIG-MTGR

                                LINE-5                                  UNIT-NO                                        ORIG-ABBR

                                LINE-6  CORONA                CA        CARRIER-ROUTE       FOREIGN-ADDR

                               

                                BILLING                                 BILLING-NAME                              PROPERTY      

                                ADDRESS                                 CO-MTGR                                   ADDRESS      CORONA                CA  92883

                                AND ZIP CORONA               CA 92883   LFM-NAME                                  CO-LFM-NAME

                                APP ID                                                                            REQ ID

                                PROP ADR VLD CODE         PROP VLD DATE             PROP VLD ID               ORG CD

                              T1231-309                                            ABC  RESIDENTIAL LENDING                                               10/17/08

                              LOAN-NO (CONT'D) 0000000000                 MASTER RECORD STATUS REPORT, REQUESTED BY ABCdhe22222                          PAGE    8

                               

                                FORECLO-STOP           0  2-TYPE,-MAN               PERCENT FLD         00.0  BRANCH-OFF          9503  2-YIELD

                                NO-NOTICES             V  2ND-ORIG-AMT         .00  9-POS-NUM              0  BLDR-BRKR           2617  2-USER-YLD

                                DONT-ANALYZE           0  2-LOAN-TERM            0  COM NUM                0  PRODUCT-LINE         7MN  2-PREPAY-PEN

                                A&H-FLAG               0  2-NEXT-PMT-#           0  PREPAY EXP D              OPT-UTIL-HDR  0100100020  LAST-UPDATED    10-17-08

                                LIFE-FLAG              0  2ND-PRIN-BAL         .00  DATE-FLD-4                 "   "    "   0010001000  INV-CHG-PROC    08-12-08

                                DISB-STOP              7  2ND-ANN-S-F    .00000000  SQUARE FOOTA        2602  TAX-ADVANCE         0.00  L-C-FEES-YTD      123.05

                                L-C-STOP                  2ND-LEV-S-F          .00  QUICK CREDIT           N  HAZ-ADVANCE         0.00  NSF-FEES-YTD         .00

                                LOAN-TERM            480  MIP-ANIV-DT        00-00  FASTRAK                N  MIP-PMI-ADV         0.00  OTH-FEES-YTD         .00

                                ORIG-DISC            .00  MIP-ANN-PREM         .00  SR LIEN DEFA              FORC-ADVANCE        0.00  OTH-FEES-DUE

                                PHONE-NO                  MIP-RMT-YTD          .00  SUBORDI NA000000000000000 INV-ADVANCE         0.00  NSF-FEE-BAL          .00

                                FNMA-STAT-CD           0  MIP-CERT-DT     00-00-00  PREPAY PASST              ATTY-ADVANCE        0.00  REMIND-DAYS

                                PMI-RATE           .0000  MIP-SERV-DT        00-00  CONCURR LN F          N   SSL-INT-C-DT              FINAL-DAYS

                                .

                                .

                                .

                                 

                                 

                              GPMLOAN  PLAN-CODE   P&I-SCHEDULE 1ST PD   2ND PD   3RD PD   4TH PD   5TH PD   6TH PD   7TH PD   8TH PD   9TH PD  10TH PD 11TH&AFTER

                              1ST-DUE-DATE   -     CHG-PD

                               

                              INS-TP AGT INSCO P-DUE EXP-DATE INSTALL TERM COVERAGE PAY COV    POLICY-NUMBER  TAX-SEQ PAYEE  MTG-ID    TAX-IDENTIFICATION-NUMBER

                              351 00000 EDIFA 01-09 01-22-09     .00  12     0000000 7 H            000000000 21 04065       11111                      111111111

                                                                                                              22 04065       11111                      111111111

                               

                              DISB-TP SEQ  PAYEE    TERM  DUE   DISB-AMT BILL NEW  U-TAX-CODE   DISB-TP SEQ  PAYEE    TERM  DUE   DISB-AMT BILL NEW  U-TAX-CODE

                                312   21 00000       12  03-09       .00   3           6          312   22 00000       12  11-08       .00   3           6

                               

                                      574,411.35 A     CALC-REMAINING-TERM     468 A

                               

                              02-08 01-16 1 61  3        .62        .00   574411.35      .00      .62       .00      .62     .00       .00       .00             1[/CODE]

                                • help with formatting
                                  Grant Perkins

                                  That's one record, right?

                                   

                                  Seems like you only want selected fields, based on your previous posts in the thread, so which parts of this data dump output are you interested in?

                                   

                                   

                                  Grant

                                    • help with formatting
                                      dezideroo98 _

                                      This depends on different requirements but as a starter, I want to pull information such as 1st-prin-bal ,escrow-bal, loan-no,line1, line 5, line 6,loan term,l-c-rate. Depending on different requirements of each business unit, the level of details for each specific report is different.

                                        • help with formatting
                                          Grant Perkins

                                          Ok, so in each case you probably need a relatively small number of the total number of fields? I assume all records are going to be at least several lines long (and even possibly all like this?)

                                           

                                          Would it be safe to say that you can always trap the start of a new record using the word CLIENT in the first line of a new record?

                                           

                                          If so here's what I would recommend (I think).

                                           

                                          Set up a PAGE HEADER template to cover the 2 page header lines and make them invisible to Monarch's parsing of the detail template.

                                           

                                          For the DETAIL template select a number of lines for the data sample. How many lines? Well, of all records are as long as the sample you could select as many lines as you need fields. So you have listed 8 fields make it 9 lines since I assume you would also need the CLIENT NUMBER as well. (I will comment on this some more in a while ...)

                                           

                                          Paint the field for CLIENT in the first line. I will always be in the same place (I assume) so no tricks required for that one.

                                           

                                          On the next line paint a field in the horizontal position where 1st-prin-bal will appear. Name appropriately and then set the advanced field properties to "Start field on" a preceding string of "1st-prin-bal ". (Quote marks not to be included). You should now see the occurrences of 1st-prin-bal field identified throughout the report.

                                           

                                          Repeat this for the other fields you require and position them in the table or summary in the order you need to display them.

                                           

                                          That's it.

                                           

                                          [I][U]Constraints.[/U][/I][/B]

                                           

                                          No two fields can be painted to overlap on a single line but there is nothing at all to stop you painting more than one field on the same line with either the same or different preceding strings so long as they do not overlap. The order of creating the fields is not important UNLESS the preceding string may not be unique enough for precise selection in which case the order may still not be important but the preceding string might be. I don't think you will have a problem with your report since I suspect that the 'tags' for the fields are the field names from the source database.

                                           

                                          If you have any very short records things may be more exciting. The number of lines set in the SAMPLE you use defines the MINIMUM number of lines any individual record might have. i.e. the smallest record.

                                           

                                          If you need more fields than you can fit into the lines in the sample, even when the fields can be defined as more than one to a line, then you would need to extend the concept a little by adding one or more Append templates using the same trap and the same number of sample lines. I don't think that will be a problem for you here, based on the information available to me so far, but if it is we can pick up on it when you identify the need.

                                           

                                          HTH.

                                           

                                           

                                          Grant.

                                            • help with formatting
                                              dezideroo98 _

                                              Hi Grant

                                               

                                              Thanks for the suggestion. Originally I did that, but the number of fields used for trapping created an issue that I had previously stated. For example if the details line have 5 lines, then you would get 5 lines of data, where no field is on the same line. So the end result is that the report will have 5 lines per record and look awkward.In this example, I wanted to have all 5 fields per record on 1 line only instead of on 5 lines.

                                              I thought of using the voodoo monarch tip, you sent me, and from there parse for the fields I want.But the issue here is how to define the end of the template. I only have Monarch 7.o version.

                                                • help with formatting
                                                  Grant Perkins

                                                  Hi Grant

                                                   

                                                  Thanks for the suggestion. Originally I did that, but the number of fields used for trapping created an issue that I had previously stated. For example if the details line have 5 lines, then you would get 5 lines of data, where no field is on the same line. So the end result is that the report will have 5 lines per record and look awkward.In this example, I wanted to have all 5 fields per record on 1 line only instead of on 5 lines.

                                                  I thought of using the voodoo monarch tip, you sent me, and from there parse for the fields I want.But the issue here is how to define the end of the template. I only have Monarch 7.o version.[/quote]

                                                   

                                                  Hmm. You've lost me.

                                                   

                                                  Lets assume you need the Client number plus 6 data fields and the you are, in the worst case scenario, unable to fit more than one field per line due to overlap problems.

                                                   

                                                  From the line with CLIENT at the start, sample for 6 lines in a block and use that block as the template sample. The data content does not really matter.

                                                   

                                                  Trap the template using the word CLIENT. Map the CLIENT NUMBER field on line 1.

                                                   

                                                  On line 2 use the preceding string idea to map another of the fields - it does not matter which. And so on for the other lines and fields.

                                                   

                                                  Your table should now have a single row for each client with the Client Number and the 5 other fields.

                                                   

                                                  If you don't get the one line result then I am very baffled.

                                                   

                                                  HTH

                                                  Grant

                                                    • help with formatting
                                                      dezideroo98 _

                                                      Hi Grant,

                                                       

                                                      That is precisely what baffled me. In the example I gave, I chose a 5 line template. I expected to have 1 line of data, but got several lines of data,as per enclosed sample.

                                                      loan     Inv     Cat     mers_reg_flag     inv_loan     prepay_ind     orig_occ_stat     mers_min     mers_reg_date     escow_bal     suspense_bal     late_charge_acc_bal     phone_number

                                                      0000000472     C01     004           xxxxxxxx594                         0.00     0.00          

                                                      0000000472                                                       492.20     

                                                      0000000472                                                            

                                                      0000000472                                                            

                                                      0000000472                                                            (555)xxx-xx95

                                                      0000000472                                                            

                                                      0000000472                                                            

                                                      0000000472                         N                                   

                                                      0000000472                                                            

                                                      0000000472                                                            

                                                      0000000472                                          .0912500                         

                                                      0000000472                                                            

                                                      0000000472                                                            

                                                      0000000472                              1                              

                                                      0000000472                                                            

                                                      0000000472                                                            

                                                      0000000472                                                            

                                                      0000000472                                                            

                                                      0000000472                                                            

                                                      0000000472                                   PER-CHG   .0000                         

                                                      /CODE

                                                        • help with formatting
                                                          Grant Perkins

                                                          Using the instructions I posted above on the report data sample you posted the concept works fine for me.

                                                           

                                                          It only needs one detail template based on the discussions so far. Add a Page Header template for completeness, just in case.

                                                           

                                                          Your example output is very difficult to follow - I assume it does not relate to the data in the posted report sample? I also have some difficulty trying to work out what the column headers mean and relate to and indeed whether the column headings and data line up at all.

                                                           

                                                          Also why did you elect to use a 5 line template? It probably does not matter at this moment, 5 lines may be enough, though you seem to have included more fields in the output than were mentioned as being those required in an earlier post. No matter, that can be allowed for, but I am curious to understand why you settled for 5 lines when more are available. I doubt the number is having any effect on the results but I am curious to understand your thinking.

                                                           

                                                          Going back to your post of 21st November, what exactly do you mean by "...  but the number of fields used for trapping created an issue  ..."?

                                                           

                                                          As far as I can tell the trap you can use should be very simple and I can't think why the number of fields should be important at all with respect to basic template trap. It is, however, a consideration when using the Preceding String method to identify fields within the template.

                                                           

                                                          Just follow my previously posted guide (I have corrected a couple of small typos) and create a new model (don't edit the old one in case it contains some hidden anomalies) and see what happens. Use the sample data record you posted earlier so that we know we are working on common ground and take it from there.

                                                           

                                                          HTH.

                                                           

                                                           

                                                          Grant

                                                            • help with formatting
                                                              dezideroo98 _

                                                              Grant,

                                                               

                                                              From my posting, there were 13 fields that I need to pick data .And yes, the data in my report did not seem to align correctly. The issue I had is that initially, I chose a larger template( more than 5 lines) and that was the report sample I posted. I then went back and selected only 5 lines templates,because  the data when coming out looked like the sample I posted,but instead had only 5 lines. For the sake of the project completion, I used a 5 lines template,because later in Excel I could manually edit the report with ease . As stated previously in my posting, I wanted to have all data in one row( client#,......, phone number). But instead of that I had 5 rows of data, so I had to cut and paste in excel the report to have all data fields aligned in one row. Hope this is clear.

                                                                • help with formatting
                                                                  Grant Perkins

                                                                  Client No prepay Exp D 1-orig-irate eg_0000000000 l-c-fees-ytd INV CAT inv-loan-no

                                                                         874     .0275000 0000000000     123.05   C01  004 xxxxxxxxx

                                                                  • help with formatting
                                                                    Grant Perkins

                                                                    I created a model with a detail Template and picked a few, sometimes random, fields to report. Here's what I got, with the report column header defining what was possible.

                                                                     

                                                                     

                                                                    CLIENT NO   PREPAY EXP D  1-ORIG-IRATE  LOAN NUMBER L-C-FEES-YTD  INV CAT     INV-LOAN-NO  SUSPENSE-BAL  PHONE-NO 

                                                                           874                   .0275000   0000000000      123.05      C01  004  xxxxxxxxx           .00             

                                                                     

                                                                    /code

                                                                     

                                                                    The results appear on a single line in my table screen - sample posted above in this post.

                                                                     

                                                                    I would have picked some more fields but the names in your extraction sample don't always seem to match the fields tags available so I would be guessing which fields you used. However, I don't think the number of fields is important here. A few fields all on one line seems to be adequate to demonstrate that the one line concept is possible.

                                                                     

                                                                    Which previous post specified and identified the 13 files you require? Early posts semed to have fewer fields referenced. That said how many is not really important to the concept of the solution.

                                                                     

                                                                    Your sample line selection for the template need to include enough lines to ensure that you do not exceed the number of lines in the SMALLEST record that may be reported and also does not require any fields to overlap. I can't be sure now many lines you actually need for the fields you want to extract because I can't readily identify all of the fields you have extracted. I suspect that your trap creates multiple detail records within what you wish to view as a single detail record but I'm not able to identify why that should be from the information available, so in that sense things are not at all clear.

                                                                     

                                                                    Just for now, put to one side everything that you did originally and try creating a new model based on my previous guidelines. As is, no adjustments or adaptations outside my recommendation at this time. Use the sample record you posted so we are both working with a consistent view of the data.

                                                                     

                                                                    See if that works for you.

                                                                     

                                                                    If it does not we may then be able to identify where the differences are coming from and so be able to arrive at an explanation of what is going on.

                                                                     

                                                                     

                                                                    HTH.

                                                                     

                                                                     

                                                                    Grant