5 Replies Latest reply: May 15, 2014 10:04 AM by Kaushals _ RSS

    Complicated Report

    Kaushals _

      I have a very complicated report to pull data.

      1. I need to capture loan number which start with '500000...'

      2. Due Date whic is in format MM-YY (ex. 07-09)

      3. PayeeType which is 313 or 320 ( on the second line)

      4. PayeeSequecne which is 21 or 01 (on the second line)

      5. PayeeCode which is char value of 9 (ex. 190070504 but can have alphabets. This is also on second line)

      6. Repeat step 5 if more than one payee exists

      7. I need to capture taxcode which is a 50 char long (see the last two lines)

       

      Here is the caviat.

      1. I can have multiple payeecode for each loan so I can have multiple taxcode. For example, See report block 1, I have two payeecode (190070504 & ATTYNADEL) and two corresponding last 2 lines which has taxcode for each payee.

       

      (##### sings represent blank space.)

      I hope this make sense to you all you guys.

       

      -


      5000000587 101 07-09 .00 12

      #########313 21 190070504 GREGORY EDWARD P 1276 Main St

      #########12-09 931.12 12

      #########313 22 190070504

      #########04-10 2,693.88 12

      #########320 01 ATTYNADEL

      + L 0064413643 11-29-08 21 190070504 27-59-5210A-236545 21239

      ##################01 ATTYNADEL 27-59-5210A-236545

       

      -


       

      5000001932 101 02-45 .00 12

      #########313 01 371012001 PRICE PAULINE 6622 N Smith ST

      Z                      11-29-08 01 371012001 10-1-1046-00 19126

       

      -


       

      5000011857 101 09-09 107.89 12

      #########312 01 101210000 3180 MASON DR NE ATLANTA GA

      #########08-09 4,647.29 12

      #########313 01 101210006

      #########08-09 .01 12

      #########320 01 101211101

      R                     05-14-09 01 101210000 17 -0061-0016-078-8 30305

      ##################01 101210006 17 -0061-0016-078-8

      ##################01 101211101 17 -0061-0016-078-8

       

      -


       

        • Complicated Report
          Kaushals _

          I was able to capture loan data. For example, from the sample below

           

          5000000587 101 07-09 .00 12

          #########313 21 190070504 GREGORY EDWARD P 1276 Main St

          #########12-09 931.12 12

          #########313 22 190070504

          #########04-10 2,693.88 12

          #########320 01 ATTYNADEL

          /CODE

           

          I was able to get the loan number (5000...), due date (07-09), due amt (0.00 , 931.12, 2693.88) type (313,320 etc.), payeecode (190070504, ATTYNADEL) and was able to store in an excel file. Now, from the sample below

           

          5000000587 101 07-09 .00 12

          #########313 21 190070504 GREGORY EDWARD P 1276 Main St

          #########12-09 931.12 12

          #########313 22 190070504

          #########04-10 2,693.88 12

          #########320 01 ATTYNADEL

          + L 0064413643 11-29-08 21 190070504 27-59-5210A-236545 21239

          ##################01 ATTYNADEL 27-59-5210A-236545

          /CODE

           

          I need to capture loan number (50000...), Payeecode (1900070504, ATTYNADEL from last 2 lines) , Tax code (27-5905210A.... from last 2 lines) and join with the external excel table which I created earlier.

          Now, the question is how do I do the second part?

            • Complicated Report
              Data Kruncher

              Hi Kaushals and welcome to the forum.

               

              Is it accurate to say that there will be a maximum of two possible TaxCode lines per group?

               

              Does this look like the output you'd like to achieve?

               

              LoanNumber    DueDate  PayeeType  PayeeSequence    PayeeCode    TaxCode

              5000000587    07-09    313        21               190070504   

              5000000587    07-09    313        22               190070504   

              5000000587    07-09    320        1                ATTYNADEL    27-59-5210A-236545

              5000001932    02-45    313        1                371012001   

              5000011857    09-09    312        1                101210000   

              5000011857    09-09    313        1                101210006    17 -0061-0016-078-8

              5000011857    09-09    320        1                101211101    17 -0061-0016-078-8[/FONT]

              /code

                • Complicated Report
                  Kaushals _

                  Thanks for replying. Yes, my output would be similar.

                  LoanNumber    DueDate  PayeeType  PayeeSequence    PayeeCode    TaxCode

                  5000000587    07-09    313        21               190070504    27-59-5210A-236545

                  5000000587    07-09    313        22               190070504    27-59-5210A-236545

                  5000000587    07-09    320        1                ATTYNADEL    27-59-5210A-236545

                  5000001932    02-45    313        1                371012001    10-1-1046-00

                  5000011857    09-09    312        1                101210000    17 -0061-0016-078-8

                  5000011857    09-09    313        1                101210006    17 -0061-0016-078-8

                  5000011857    09-09    320        1                101211101    17 -0061-0016-078-8

                  /code

                   

                  How do I achieve that? I am new and this is my first report to work on

                    • Complicated Report
                      Data Kruncher

                      I am new and this is my first report to work on[/quote]My aforementioned welcome to the forum should then have been a welcome to the deep end of the pool.

                       

                      It's all good. There's no better way to learn this stuff than to have a challenging real-world task to accomplish.

                       

                      First up, thanks for the great description of the report. That really helps.

                       

                      The first task is the detail template. I used the line with the PayeeType, and also painted PayeeSequence and PayeeCode. I used 3NN as the trap characters for the PayeeType.

                       

                      To that I appended the LoanNumber and DueDate using 5NNN as the LoanNumber trap.

                       

                      Finally, a footer template (a 2 line sample) for a PayeeCodeTaxCode combination field. It's the last line in your sample that make this whole exercise more interesting, as you've probably guessed by now.

                       

                      On the Advanced tab of the PayeeCodeTaxCode field, I elected to end the field on the end of left justification.

                       

                      OK, that's it for the templates; off to the Table window.

                       

                      I created four new calculated fields to deal with the footer information. The idea is that the TaxCode only applies to the matching PayeeCode, and we need to match them correctly.

                       

                      PossibleTaxCode1 needs this formula:

                      LSplit(textline(,1),2," ",2)[/code]PossibleTaxCode2:

                      LSplit(textline(,2),2," ",2)[/code]TaxCodePayee1:

                      LSplit(textline(,1),2," ",1)[/code]TaxCodePayee2:

                      LSplit(textline(,2),2," ",1)[/code]and finally, to get the right info on the right record, TaxCode is:

                      if(PayeeCode=[TaxCodePayee1],[PossibleTaxCode1],

                      if(PayeeCode=[TaxCodePayee2],[PossibleTaxCode2],""))[/code]Then just hide the behind the scenes calculations in the Table (maybe use the field list dialog - look on the far right side)  so that only the necessary fields are displayed, as I'd posted earlier.

                       

                      Of course, if your footers have more than 2 lines elsewhere in the report, then a refinement to the solution is needed. In fact, other members may well have an entirely different approach to your challenge.

                       

                      This really is one of what I believe Monarch's great strengths: there's rarely one "right" way. It's flexibility often allows for multiple solutions to the same end result. As fellow member Grant Perkins often says, experimentation is the best way to learn Monarch. It's usually quick and easy to determine what's going to work and what won't for a particular challenge.

                       

                      If you're not yet familiar with them, have a look at the online help for details on the TextLine and LSplit functions, or you can review [URL="http://********************/category/functions/"]my site[/URL] for additional information on Monarch's functions (and much more, of course).

                       

                      Shout if you have further questions.

                       

                      HTH,

                      Kruncher

                        • Complicated Report
                          Kaushals _

                          Thank you. It worked. So I was able to follow your instructions and created detail, append and footer templates. In the footer template, I do not need to split the code as I was able to select them as seperate fields. Below is my output. The fifth column is not correct but last column is correct and that's what I wanted. I can hide the fifth column. One thing I did not understand is the role of footer. How does it work? I mean how did the data from footer is appended to each record?

                           

                          5000011857     09-09     107.89     12     101210000           17 -0061-0016-078-8     312     101210000

                          5000011857     08-09     4647.29     12     101210000           17 -0061-0016-078-8     313     101210006

                          5000011857     08-09     0.01     12     101210000           17 -0061-0016-078-8     320     101211101

                           

                          /code