7 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Help with multi-line trapping

    lanman00 _

      I have a trapping problem...  Sample data for 3 days is below...  I am using Monarch Pro V8...

      I need to trap each day's variable multi-line record into two fields per day: date and detail.  The detail would be split using calculated fields.

      Any suggestions? 

       

      Thanks in advance for your help. 

      lanman00

       

      20050901    1Cash           445.78

      20050901    2Check           50.00

      20050901    3MC/VISA         21.40

      20050901    7INSTL BILL    1405.30

      20050902    1Cash           450.02

      20050902    2Check           85.75

      20050902    3MC/VISA        147.86

      20050902    7INSTL BILL     840.10

      20050902   14INSTL PAGE      99.25

      20050906    1Cash           571.29

      20050906    2Check          100.00

      20050906    3MC/VISA        119.81

      20050906    7INSTL BILL    1619.66

      20050906   14INSTL PAGE      50.00

        • Help with multi-line trapping
          Grant Perkins

          I am slightly confused as to why you want to create a single multiline field and then split it when you already have the data split out.

           

          However, one way to do what you describe (as I read it at least) would be to template for 2 fields - 'Date' and 'the rest'. Create a 2 field summary. Suppress duplicates on the date column and export the result to a text file report.

           

          Open the new report in Monarch. Ignore the extra count column it will have. Trap for the datye and define the second column(s) as a multiline field. Use the date as an append. That will give you the two fields which you can then separate once more using calculated fields.

           

          On the other hand if you provide an example of what the posted data needs to look like when you have finished with it there may be a few alternative approaches to consider.

           

          HTH.

           

          Grant

          • Help with multi-line trapping
            lanman00 _

            Grant,

             

            The end result needs to be exported to an existing Access database table...

             

            record layout (fields):

            date         cash  check  credit

            9/01/2005  445.78  50.00   21.40

            9/02/2005  450.02  85.75  147.86

            9/06/2005  571.29 100.00  119.81

             

            lanman00

            • Help with multi-line trapping
              Grant Perkins

              OK, try this.

               

              Single template trap on whatever  - 3 numbers at the begining of the line for example.

               

              Paint the date field as normal. Paint a field for the line type NUMBER AND DESCRIPTION. We will use the numbers to keep the order consistent - you can refine the appearance later if required. Paint a field for the amounts.

               

              Check the table.

               

               

              Create a summary using Date and the Line Type number/desc field and them the amount as a SUM in the MEASURE section.

               

              Right click on the Line Type/Desc field in the KEY field, click the DISPLAY tab and set the display to ACROSS.

               

              View the summary without drilling up. You will see what you need but with the addition of a summary column and final row. What you may want to do with them is another matter and how to do it will depend upon your preferred method for exporting to Access. Are you automating this or using cut and paste? C&P would simpl0y require you to not cut the last row and column from the summary when selecting.

               

              Automating may require a different approach.

               

              You could also export to a fixed field width text file and run a Monarch model on the new file ignoring the last column and row, then expert the result of that.

               

              HTH.

               

               

              Grant

               

              Edit to add:  Unless of course you want to combine several different fields into a single CHARGE field, in which case a little more data prep is required.

               

              To do that I would categorise each of the existing line types using a calculate field to set either Cash Credit of Charge. Then use that new category group field as the second key in 'across' mode.

              • Help with multi-line trapping
                lanman00 _

                Grant,

                Thanks... Your suggestion worked. I will use c&p method to add data to Access table.

                 

                Here's what I did...

                1. Created a single line trap to capture date, location, payment type and amount.

                2. Created calculated fields as required to split data into necessary parts and added a filter on the table to exclude unwanted payment types.

                3. Created a summary with key fields date (display=down) & payment type (display=across) and measure sum(amount).

                 

                Thanks again.

                 

                lanman00

                • Help with multi-line trapping
                  Grant Perkins

                  Glad it worked!

                   

                  I wondered if you were going to filter out some of the line types or just aggregate them.

                   

                  Slightly puzzled as to why you can't simply pick the fields you want rather than have to take the entire line and slice and dice it. Nothing wrong with slicing and dicing - in fact I quite enjoy doing it. But it does seem odd that you need to do it for this. Is the real file not quite like the one you posted?

                   

                  No matter. That you have a solution is the important thing.

                   

                  Grant

                  • Help with multi-line trapping
                    lanman00 _

                    Grant,

                    I think you helped me with a Monarch problem several months ago.

                    Here's why I need to do it this way... The final database table that will receive this data is fixed and was created with an entirely  differently formatted data source (Bank Administration Institute (BAI) Level 2). In order to continue to use the existing application, I need to create a record layout that "fits" into the data structure in the defined, correct sequence and format, so I need to fabricate some extra fields to append these fields for each transaction created from this datafile. I have used calculated fields in other models to populate these extra fields such as filename, upload date, format type, etc. that really aren't part of the data but are needed in order to paste the data into Access. There may be a better way, but I do this just once a month, so a quick & dirty external data manipulation & reformatting solution is preferable to rewriting or creating a new application.  Your suggestion saved me a lot of grief. Thanks.

                    lanman00

                    • Help with multi-line trapping
                      Grant Perkins

                      Originally posted by lanman00:

                      Grant,

                      I think you helped me with a Monarch problem several months ago.

                      Here's why I need to do it this way... The final database table that will receive this data is fixed and was created with an entirely  differently formatted data source (Bank Administration Institute (BAI) Level 2). In order to continue to use the existing application, I need to create a record layout that "fits" into the data structure in the defined, correct sequence and format, so I need to fabricate some extra fields to append these fields for each transaction created from this datafile. I have used calculated fields in other models to populate these extra fields such as filename, upload date, format type, etc. that really aren't part of the data but are needed in order to paste the data into Access. There may be a better way, but I do this just once a month, so a quick & dirty external data manipulation & reformatting solution is preferable to rewriting or creating a new application.  Your suggestion saved me a lot of grief. Thanks.

                      lanman00 /b[/quote]OK, I understand and sort of recall something like that from the past. I guess it would also be possible to extract the fields as they are presented (no C&P) and then use calculated fields drawing in the data from the extracted field to set field type and size, etc. By the time you have done that my guess is that the work would be much the same either way. So keeping things consistent with your existing process makes sense.

                       

                      I'm glad the suggestion gave you a basis for a solution.

                       

                       

                      Grant