3 Replies Latest reply: May 15, 2014 10:11 AM by Olly Bond RSS

    Help w/ trapping fields into excel on a unique report

    LaurieSoto _

      Using: Monarch Pro v9

      Trying to create a custom model that includes every field on this page and struggling w/ the fact that the fields go up, down and wrap over to the next column as opposed to straight across. If I trap a single line that reads (Acct Staus, current, MTD, consumer info indicator, curr baase..etc), that line will change midway down the page and the line will then read (special comment, curr, mtd, ECOA code, curr base...etc) the same lines change and at times overlap which makes me even more furstrated. in the sample provided, I need to capture Acct Status, Consumer Info Indicator, Special comment, ECOA code, Compliance condition code and Client Entered Overide as seperate columns within Excel and can't determine how to do so w. the overlapping issue.

       

                    BANK                            DRAPER, UTAH                         IDENTIFICATION NO  XXXXXX      

                   ACCT STATUS                             CONSUMER INFORMATION INDICATOR    CURR      CURR            MTD            MTD

                                     CURR             MTD                                    BASE     J1 J2            BASE          J1 J2

      05  SOLD/TRANSFERRED             0               0  A PETITION CHPTR  7 BNKRPT           0         0               0              0

      11  NORMAL ACCT                  0               1  B PETITION CHPTR 11 BNKRPT           0         0               0              0

      13  CLOSED ZERO BAL              0               0  C PETITION CHPTR 12 BNKRPT           0         0               0              0

      61  PD VOLUNTARY SURREND         0               0  D PETITION CHPTR 13 BNKRPT           0         0               0              0

      62  COLLECTION PAID FULL         0               0  E DISCHRGD CHPTR  7 BNKRPT           0         0               0              0

      63  PAID REPOSSESSION            0               0  F DISCHRGD CHPTR 11 BNKRPT           0         0               0              0

      64  PAID CHARGE OFF ACCT         0               0  G DISCHRGD CHPTR 12 BNKRPT           0         0               0              0

      65  PD VOLUNTARY SURREND         0               0  H DISCHRGD CHPTR 13 BNKRPT           0         0               0              0

      71  1 CYCLES DELINQUENT          0               0  I DISMISSD CHPTR  7 BNKRPT           0         0               0              0

      78  2 CYCLES DELINQUENT          0               0  J DISMISSD CHPTR 11 BNKRPT           0         0               0              0

      80  3 CYCLES DELINQUENT          0               0  K DISMISSD CHPTR 12 BNKRPT           0         0               0              0

      82  4 CYCLES DELINQUENT          0               0  L DISMISSD CHPTR 13 BNKRPT           0         0               0              0

      83  5 CYCLES DELINQUENT          0               0  M WITHDRWN CHPTR  7 BNKRPT           0         0               0              0

      84  6 CYCLES DELINQUENT          0               0  N WITHDRWN CHPTR 11 BNKRPT           0         0               0              0

      88  CLAIM FILED WITH GOV         0               0  O WITHDRWN CHPTR 12 BNKRPT           0         0               0              0

      89  DEED RECEIVED                0               0  P WITHDRWN CHPTR 13 BNKRPT           0         0               0              0

      93  SERIOUSLY PAST DUE           0               0  Q REMOVE PREV   CODE REPTD           0         0               0              0

      94  FORECLOSURE COMPLETE         0               0  R REAFFIRMATION OF DEBT              0         0               0              0

      95  VOLUNTARY SURRENDER          0               0  S REMVE REAFFRM OR RESCIND           0         0               0              0

      96  MERCHANDISE REPOSSES         0               0  T CAN NOT LOCATE CONSUMER            0         0               0              0

      97  CHARGE OFF                   0               0  U CONSUMER NOW LOCATED               0         0               0              0

      DA  DELETE BUREAU RECORD         0               0  V REAFFRM RESCNDED CHPTR 7           0         0               0              0

      DF  DELETE RECORD FRAUD          0               0  W REAFFRM RESCNDED CHPTR 11          0         0               0              0

                                                           X REAFFRM RESCNDED CHPTR 12          0         0               0              0

                SPECIAL COMMENT                            Y REAFFRM RESCNDED CHPTR 13          0         0               0              0

      AB  INSURANCE PAID DEBT          0               0  Z BANKRUPTCY-UNDSNED CHPTR           0         0               0              0

      AC  PARTIAL PAYMENT              0               0 1A PERSONAL RECEIVERSHIP              0         0               0              0

      AH  PURCH BY OTH                 0               0 2A LEASE ASSUMPTION                   0         0               0              0

      AI  MILITARY DUTY                0               0                                                                               

      AP  CREDITLINE SUSPENDED         0               0                                                                               

      AT  ACCOUNT TRANSFER             0               0      ECOA CODE                                                                 

      AU  SETTL<FULL BAL               0               0  0 UNDESIGNATED                       0         0               0              0

      AV  FIRST PYMT NT RCVD           0               0  1 INDIVIDUAL                         0         0               1              0

      AW  NATURAL DISASTER             0               0  2 JOINT CONTRACT                     0         0               0              0

      B   CREDIT COUNSELING            0               0  3 AUTHORIZED USER                    0         0               0              0

      BL  LOST - STOLEN                0               0  4 JOINT NOTA 2                       0         0               0              0

      BO  FORECLOSURE STARTED          0               0  5 CO-MAKER                           0         0               0              0

      BT  PRINCIPAL DEFERRED           0               0  6 ON-BEHALF OF                       0         0               0              0

      CI  CLSD-INACTIVITY              0               0  7 MAKER ACCT                         0         0               0              0

      CJ  CREDITLINE NOT AVAIL         0               0  T TERMINATED BORROWER                0         0               0              0

      CK  CREDITLINE REDUCED           0               0  X DECEASED CONSUMER                  0         0               0              0

      CL  CREDITLINE SUSPENDED         0               0  Z DELETE BORROWER                    0         0               0              0

      CM  COLLATERAL RELEASED          0               0                                                                               

      CN  LOAN MODIFIED                0               0                                                                               

      M   CLSD-BY GRANTOR              0               0                                                                               

      O   AT TO OTHER LENDER           0               0                                                                               

      S   SPECIAL HANDLING             0               0                                                                               

      ZA  ORDERLY PAYMENT              0               0                                                                               

      ZB  VOLUNTARY DEPOSIT            0               0                                                                               

      ZC  SKIP TRACE                   0               0                                                                               

      ZD  CONSUMER DISPOSAL            0               0    CLIENT ENTERED OVERRIDES                                                                               

      CURR             MTD                                                                               

      ACTUAL PAYMENT AM                0               0                                                                               

      CHARGE-OFF AM                    0               0                                                                               

      CLOSED DATE                      0               0                            

          COMPLIANCE CONDITION CODE                        COMPLIANCE COND CODE             0               0                            

      XA CLSD BY CNSMR (CBC)           0               0  CREDIT LIMIT AM                  0               0                            

      XB ACCOUNT INFO DISPUTE          0               0  CURRENT BALANCE AM               0               0                            

      XC DSPT CMPLT/DSAGRE             0               0  FCBA DISPUTE CODE                0               0                            

      XD CBC/IN FCRA DSPT              0               0  FCRA DISPUTE CODE                0               0                            

      XE CBC/DSPT CMPLT/DSAGRE         0               0  LOAN AM/HIGH BAL AM              0               0                            

      XF ACCOUNT FCBA DSPT             0               0  METRO ACCT STATUS CODE           0               0                            

      XG FCBA DSPT CMPL/DSAGR          0               0  PAST DUE AMOUNT                  0               0                            

      XH DSPT RSLVD/DATA FURN          0               0  PAYMENT HISTORY                  0               0                            

      XJ CBC/IN FCBA DSPT              0               0  PAYMENT RATING CODE              0               0                            

      XR REMVE MOST RECENT CCC         0               0  SPECIAL COMMENT CODE             0               0                            

       

       

       

      TOTAL BASE REC                  0                1 J1 J2 REC              0                0  K2 REC            0                0 

            K4 REC                    0                0 L1 REC                 0                0  N1 REC            0                0 

      TOTAL BALANCES ON TAPE                 0        TOTAL DELIQUENT BALANCE                 0  

      /CODE

        • Help w/ trapping fields into excel on a unique report
          Olly Bond

          Hello Laurie, and welcome,

           

          This looks fairly regular, and certainly trappable, but I'm not sure about the REAFFIRM RESCNDED CHPTER 12 line. this isn't like the other lines with a two character code, nor is it like the SPECIAL COMMENT line that follows. Am I safe in assuming that every detail record is wrapped to exactly two lines?

           

          Best wishes,

           

          Olly

            • Help w/ trapping fields into excel on a unique report
              LaurieSoto _

              Hey Olly, thanks and I appreciate your response! 

               

              I'm used to trapping detail that goes straight across, but in this case it's unique in the sense that each is grouped in a downward fashion, not straight across. if you look at the Acct status group of columns they don't match up w/ the group of Consumer Info Indicator, then midway down the page - 1st 3 columns change from Acct status group to Special comment group - yet the consumer info indicator grouping is still going for a couple of rows before that in fact changes to ECOA code and every grouping needs to be detail and on it's own column when converted to excel.

                • Help w/ trapping fields into excel on a unique report
                  Olly Bond

                  Hi Laurie,

                   

                  My bad - I was looking at your sample on an iPad and the wrapping was all wonky. Now I can see the sample properly, my reply looks like nonsense

                   

                  You might be able to get some way towards what you want with a multi column region. Define a single line detail field wide enough to cover the whoe line, say about 100 characters.

                   

                  Then turn on multi-column region with 2 columns, each 1 character wide, starting at position 101. This will give you two identical rows of data in the table for each line in your report.

                   

                  Assume your big field is called , then you can use if(Column()=1;substr(Detail;1;50);substr(Detail;51;50)) to convert each of these into chunks of the original report lines.

                   

                  Hope this helps,

                   

                  Olly