4 Replies Latest reply: May 15, 2014 9:56 AM by Gareth Horton RSS

    Help: Report has records with 1 or 2 Lines

    Phil Samuels

      I'm a frustrated man!  I've been working on this report for hours.  It's a simple report where a record can have 1 or 2 lines depending on whether the borrower of a loan has been assessed a late charge. Also, the same loan can appear more than once on consecutive lines if more than one payment was received in a month.  Here's a simplified example:

      LOAN #  PYMT DATE  LOAN AMT  PYMT RECD 

           LATE CHG   DAYS LATE

      246433   12-01-04  2344.33    345.43

      246433   12-30-04  2344.33    100.00

      765433   12-01-04  1754.33    247.43

           17.54         12

      765433   12-27-04  1754.33    229.91

      654333   01-03-05  1000.00    167.54

      224564   12-24-04  1643.00    210.04

      321453   12-27.04  7554.33    654.32

           23.54         31

      876565   12-27-04  2100.00    234.00

      -


       

      As you can see, sometimes a record contains 1 line while other times, there are 2 lines.  It's a simple report.  The fields are always in the same place on either line 1 or line 2.  I tried various "append" traps, but I can't get a SINGLE RECORD to contain all of the fields on 1 line for a 1-line record or both lines for a 2-line record.

       

      I'd appreciate any help and I'd gladly pay for advice.

       

      Thanks.

        • Help: Report has records with 1 or 2 Lines
          Grant Perkins

          Phil,

           

          It's a pity the second lines do not start to the right a few characters, our options would be that much better perhaps.

           

          OK, I think there are 3 or so approaches we could consider.

           

          First Idea[/b]

           

          Pre-process the report BEFORE opening in Monarch to put each record on one line. It should be possible to so this using MSRP to convert anything (? maybe care required at the end of report point) that has an "end of line" followed by a specified number of spaces to be a single space instead.

           

          Process using the revised report should be easy.

           

          Second Idea[/b]

           

          A 2 stage process. Best to have Monarch Pro for this.

           

          Stage 1 selects ONLY the second lines and appends the fields for Loan# and PAYMT date.

           

          Export the resulting table to a file or Excel spreadsheet.

           

          Stage 2 rund a model that picks only the Loan# lines and then doe a look up compare to the previously exported late payment table to obtain the amount and number of days late.

           

          Third Idea[/b]

           

          Create a template to trap on the Loan#, paint a field for the entire line width as a single field. Use the Advanced field properties to make this a multi-line field ending only when the next record is reached. (last option of those available in "End field on" but the text varies according to which version you are using).

           

          This should result in a single "field" per record. Now you can "slice and dice" the field using functions - probably LSPLIT - to break the field up into calculated fields for the parts of the data you require.

           

          If you records are pretty much as posted I would go for option 3 - it's not too much work as far as I can tell. (Something may have been lost in the format during posting though so I will be cautious about that claim!)

           

          I hope this helps.

           

          Grant

           

           

          Originally posted by Phil Samuels:

          I'm a frustrated man!  I've been working on this report for hours.  It's a simple report where a record can have 1 or 2 lines depending on whether the borrower of a loan has been assessed a late charge. Also, the same loan can appear more than once on consecutive lines if more than one payment was received in a month.  Here's a simplified example:

          LOAN #  PYMT DATE  LOAN AMT  PYMT RECD 

               LATE CHG   DAYS LATE

          246433   12-01-04  2344.33    345.43

          246433   12-30-04  2344.33    100.00

          765433   12-01-04  1754.33    247.43

               17.54         12

          765433   12-27-04  1754.33    229.91

          654333   01-03-05  1000.00    167.54

          224564   12-24-04  1643.00    210.04

          321453   12-27.04  7554.33    654.32

               23.54         31

          876565   12-27-04  2100.00    234.00

          -


           

          As you can see, sometimes a record contains 1 line while other times, there are 2 lines.  It's a simple report.  The fields are always in the same place on either line 1 or line 2.  I tried various "append" traps, but I can't get a SINGLE RECORD to contain all of the fields on 1 line for a 1-line record or both lines for a 2-line record.

           

          I'd appreciate any help and I'd gladly pay for advice.

           

          Thanks. /b[/quote]

          • Help: Report has records with 1 or 2 Lines
            Bruce _

            Phil

             

            If you really want to pay, Datawatch has a service to create the models and reports for you for a fee.

             

            However Grant is giving you great FREE advice & he is the expert on this subject.

             

            Good luck

            • Help: Report has records with 1 or 2 Lines
              Grant Perkins

              Originally posted by Bruce Reed:

              However Grant is giving you great FREE advice & he is the expert on this subject.

              /b[/quote]Hehe, thanks Bruce!

               

              And to think I was just looking up my $ rates!

               

                         

               

              Oh well, I'll have to cancel the Ferrari order in the morning ...

               

               

              Grant

              • Help: Report has records with 1 or 2 Lines
                Gareth Horton

                Hi All

                 

                If the dates are not important, and you are happy to see a total of payments and charges, rather than the individual ones then there is a very easy way to do this.

                 

                Just create a summary, using Loan# as the key, with the rest as measures.

                 

                Then, to make sure the loan amount does not get summed, enter the following formula for the loan measure:

                 

                SUM(LoanAmt)/Count(*)

                 

                You should then see something like this.

                 

                [font="courier"]     Loan         Charge        Payment        Overdue RATIO(LoanAmt)

                   224564         (Null)         210.04         (Null)        1643.00

                   246433         (Null)         445.43         (Null)        2344.33

                   321453          23.54         654.32             31        7554.33

                   654333         (Null)         167.54         (Null)        1000.00

                   765433          17.54         477.34             12        1754.33

                   876565         (Null)         234.00         (Null)        2100.00[/font][/quote]Obviously you can rename the columns as necessary.

                 

                I am aware this might not be enough, but it is a very simple approach and thought it might be worth suggesting.

                 

                Gareth

                 

                Originally posted by Phil Samuels:

                I'm a frustrated man!  I've been working on this report for hours.  It's a simple report where a record can have 1 or 2 lines depending on whether the borrower of a loan has been assessed a late charge. Also, the same loan can appear more than once on consecutive lines if more than one payment was received in a month.  Here's a simplified example:

                LOAN #  PYMT DATE  LOAN AMT  PYMT RECD 

                     LATE CHG   DAYS LATE

                246433   12-01-04  2344.33    345.43

                246433   12-30-04  2344.33    100.00

                765433   12-01-04  1754.33    247.43

                     17.54         12

                765433   12-27-04  1754.33    229.91

                654333   01-03-05  1000.00    167.54

                224564   12-24-04  1643.00    210.04

                321453   12-27.04  7554.33    654.32

                     23.54         31

                876565   12-27-04  2100.00    234.00

                -


                 

                As you can see, sometimes a record contains 1 line while other times, there are 2 lines.  It's a simple report.  The fields are always in the same place on either line 1 or line 2.  I tried various "append" traps, but I can't get a SINGLE RECORD to contain all of the fields on 1 line for a 1-line record or both lines for a 2-line record.

                 

                I'd appreciate any help and I'd gladly pay for advice.

                 

                Thanks. /b[/quote]