18 Replies Latest reply: Nov 7, 2014 1:10 PM by Olly Bond RSS

    Multiple line trap

    Peter _

      Can anyone help.

      I am trying to trap multiple lines.

      SMITHHH, JUDITH A REGULAR 18.55 1484 1136  20991.4

         0105 0162      HOLIDAY

      123-45-6789       SICK

                        VACTION

       

      This is a payroll record. The file number is on the second line (0162).  I need to get the file number, regular pay and any other earnings in a row.  I have tried every combination I can think of. The best I can do was have the file number skip the first record and be off by one.

      Thanks

      Peter

        • Multiple line trap
          Winn _

          Peter,

           

          A couple of questions:

           

          1. Are you using a multiple line trap to cover the entire record?

           

          2. Have you tried trapping the file number and setting the trap line to 2?

           

          Doing these may help get you the results that you want.

          • Multiple line trap
            Peter _

            I tried both of these options.  Trapping the hole record would not let me select all the wages, ie. vacations.  Setting the second line also would not let me select all wages and would skip first record.

             

            Peter

            • Multiple line trap
              Winn _

              OK, I think that I am beginning to understand your situation. I have one question. Your report looks something like this, correect?[quote][font="courier"]SMITHHH, JUDITH A REGULAR  18.55 1484 1136 20991.4

              0105 0162         HOLIDAY

              123-45-6789       SICK

                                VACATION

                /font[/quote]the number of lines in each record is 3 or more. So you want each person to be in a single row with the earnings for each payroll item in a seperate field.

               

              Does this sum up what you are looking for?

              • Multiple line trap
                Peter _

                Winn,

                Yes that is correct. I want my first column to be my file Number. After that my regular earning, o/t and then other earnings in separate columns.

                 

                Peter

                • Multiple line trap
                  Grant Perkins

                  Peter,

                   

                  Is your problem mainly related to creating a successful trap?

                   

                  Or is it more a difficulty in defining the fields reliably?

                   

                  I have to observe that it looks quite simple at first sight (using a cut and paste copy of your posted data record as it appears when viewed thrugh the 'Post a Reply' screen) so I wonder if I am missing something problematic.

                   

                  Are you in a position to release a sample file (confidential data changed if necessary) via email that I (or we if others are prepared to do it) can play with?

                   

                  Grant

                   

                  PS Looks like you had another exchange with Winn while I was preparing my response. I think my questions are still valid but Winn may well provide the solution for you without any need to respond to my questions.

                   

                     Originally posted by Peter:

                  Can anyone help.

                  I am trying to trap multiple lines.

                  SMITHHH, JUDITH A REGULAR 18.55 1484 1136  20991.4

                     0105 0162      HOLIDAY

                  123-45-6789       SICK

                                    VACTION

                   

                  This is a payroll record. The file number is on the second line (0162).  I need to get the file number, regular pay and any other earnings in a row.  I have tried every combination I can think of. The best I can do was have the file number skip the first record and be off by one.

                  Thanks

                  Peter /b[/quote]

                   

                  [size="1"][ August 11, 2003, 02:59 PM: Message edited by: Grant Perkins ][/size]

                  • Multiple line trap
                    Peter _

                    Grant,

                     

                    Yes, I can send the you some of it.  I have to change some of the info due to confidentiality.

                     

                    Peter

                    • Multiple line trap
                      Grant Perkins

                      Peter,

                       

                      That would be useful and I understand the need for confidentiality.

                       

                      As I am going to be away until later in the week and I fully expect Winn to provide the answers you need by then, I would suggest you hold on for  while before preparing a file (unless Winn would find it useful).

                       

                      If you do decide you want to send something, post me a Private Message with your email address and I will let you have my email address so you can send the file.

                       

                      Grant

                       

                        Originally posted by Peter:

                      Grant,

                       

                      Yes, I can send the you some of it.  I have to change some of the info due to confidentiality.

                       

                      Peter /b[/quote]

                       

                      [size="1"][ August 11, 2003, 03:05 PM: Message edited by: Grant Perkins ][/size]

                      • Multiple line trap
                        Mike Urbonas

                        Peter, you are in good hands with Winn and Grant but I will simply add that I have dealt with similar Payroll Registers.  Each employee's detail data is different (ie, one line of data -40 hours regular - for Mary, two lines of data - 32 hours regular + 18 vaca for John, etc. etc.) This varying number of detail data lines per employee makes "one size fits all" multiple line traps impossible.

                         

                        You will probably rnd up defining a trap just for the first 3 lines of each record, create fields to grab the name, file number, etc., and also use Advanced Field Properties to define a varying, multi-line field to capture the regular, sick, vaca etc. pay.

                         

                        For example, when defining your fields in Report View, highlight the word REGULAR and double click on it to get to the Field Properties window.  Click on Advanced tab and change the End Field Justification so the field will automatically include add'l data on the next line until there are no more records. 

                         

                        Go to the The Field Properties dialog in Monarch Help and read up on the Advanced Properties section.  This will explain what I am trying to describe here.  See also "Lesson 14 - Extracting Multiple Line Fields" in the Learning Guide. 

                         

                        Such an Advanced Field is the first key step in solving your report mining needs here.

                         

                        Mike

                          • Re: Multiple line trap
                            Stan Ho

                            Hi Mike,

                             

                            I'm in that exact scenario and cannot extract the remaining rows.  Every employee has at least two rows, but some have more due to addtl pay types.  Can you elaborate on how to prevent pulling in the next employee's data as the current one's?  My detail grabs the first two lines and then I have appends for each possible additional line (i.e. pay types 3 - x). 

                             

                            For example, Joe only has two lines (reg + OT), whereas Sue has four.  I made a detail and two appends (row 3 and row 4).  For Joe, my second append (row 4) grabs Sue's Reg 40.  How do I make Modeler recognize that Reg 40 belongs to Sue and not Joe?

                             

                            ***          Reg     40

                            Joe J.     OT     8

                             

                            ***          Reg     40

                            Sue S.     OT     8

                                           DBL   4

                                           Hol     8

                              • Re: Multiple line trap
                                Olly Bond

                                Hello Stan,

                                 

                                Mike Urbonas left Datawatch a few years ago - this thread is 11 years old. Nice to see that my fellow Monarch Expert Grant Perkins was helping users even back then! The challenge you face is solved in v10 or above with the introduction of the Cleared By template - if you're on an even older version there might be some fancy workarounds, but there's no guarantee you can get the data you need in one model.

                                 

                                Generally, if your Detail grabs the first lines, then the subsequent lines should be Footers not Appends. And if the Footers are Cleared By the Detail you'll get cleaner data. But we'd need to see the report to give 100% certain advice. If you'd like a model built, we can do that too, but there's a charge for that.

                                 

                                Best wishes,

                                 

                                Olly

                                .

                                  • Re: Multiple line trap
                                    Stan Ho

                                    Olly! Thanks for saving me.  Or at least trying at this point.  I still cannot get the Group Footer to pull the right info.  I've copied and pasted from Modeler, hope this is legible to you. 

                                     

                                    Here's an obfuscated sample of two employees.  The first, Deinspager has Overtime and Regular whereas the second, Acoomoova, only has Regular.  I created a Detail template to catch the first line, but how do I catch Deinspager's Regular pay?  There are two blank rows between each employee, so catching the second line of Acoomoova is just blank, but my templates for the fourth and beyond pay types starts grabbing the next employee's pay types and associating them with Acoomoova.  When I use the Detail template as the Clearing Template for the Group Footer, it still does not work.

                                     

                                       ***                       OVERTIME                  x.00       xx.5500        xx.20  FEDERAL INCOME TAX         xxx.51  ADMIN FEE                    xx.92

                                       DEINSPAGER XXXXXXX        REGULAR PAY              xx.00       xx.7000     1,991.00  FICA - MEDICARE             xx.18  MEDICARE - EMPLOYER          xx.08                                                                                                                                                            

                                                                                                                                                                                                

                                     

                                       ***                       REGULAR PAY              xx.08       xx.0000     xxxxx.12  FEDERAL INCOME TAX         xxx.75  ADMIN FEE                    xx.92

                                       ACOOMOOVA XXXXXX                                                                     FICA - MEDICARE             xx.61  MEDICARE - EMPLOYER          xx.63

                                       Pay Method   HOURLY                                                                  FICA - OASDI                xx.48  OASDI - EMPLOYER             xx.57

                                                                                                                                                                                                

                                      • Re: Multiple line trap
                                        Olly Bond

                                        Hello Stan,

                                         

                                        That is a nice challenge - having the name not appear on either the top or the bottom possible row of detail, but taking up space on a detail line as well. There are tricks - fields can be defined that only start on 2nd or 3rd text lines, that stop after the occurrence of a blank line, that only appear after defined text in a preceding string... Names can be multiplied up to 100 times using the Mufti-Column Region and then a Memo block can be parsed to slice out each line as required. A stopper template on the blank lines can help, but here it seems there are single blank lines among the data, so it will be hard to trap just the occurrence of doubles. Getting the right combination of tricks can be done two ways - you can experiment, post redacted samples here, and we can gently try to steer you towards a fix. Or you can email us the report, we'll build you a model within 24 hours, and then we'll run through the techniques we used so that you get an advanced tutorial in how to use Modeler to attack difficult data. This costs GBP 500 the same as a two day class in Modeler from Datawatch.

                                         

                                        Best wishes,

                                         

                                        Olly

                                    • Re: Multiple line trap
                                      RalphB _

                                      Stan,

                                       

                                      Have you tried setting the first line as a header?  Then set the 2nd lie as detail and the way you have set up the rest.

                                       

                                      You may want to try the "Guru Trap'" solution.  Search the site for Guru Trap and you should find some threads detailing it.

                                       

                                      HTH.

                                       

                                      Ralph

                                        • Re: Multiple line trap
                                          Stan Ho

                                          Hey Guys, thanks for your insights.  I ended up with a down and dirty solution as all the elegant ones went tragically bad.  I basically made a blank Detail that grabs everything except the page header.  Then I made every element as an Append (e.g. name, pay rate, pay type).  This produced a table with a row for every line on the original PDF.

                                           

                                          My final solution involves an Excel macro so I used the macro to also clean the Modeler export.  I'm much more comfortable in VBA so this worked well for me.  I really appreciate the input!

                                            • Re: Multiple line trap
                                              Olly Bond

                                              Hello Stan,

                                               

                                              Glad you got a fix - though Excel macros are tricky to audit or automate. What happens if the report layout changes slightly? Are you sure you've got 100%, and not 99% or 101%, or the records you need? Can you prove it? Is the process critical to your, or your client's business?

                                               

                                              Best wishes,

                                               

                                              Olly

                                      • Multiple line trap
                                        Peter _

                                        Mike,

                                        I can get the data that I need. I need to have the File Number in each record. The problem is the File number is on the second line. It seems I can only get the File Number or the Payroll info, but not both.

                                         

                                        Peter

                                        • Multiple line trap
                                          Grant Perkins

                                          Peter,

                                           

                                          I know where Mike is coming from and he offers a good solution as would be expected. But I reckon you should have no problem if you can get all the data you need.

                                           

                                          If the record formats are ALL 4 rows (it is a little more complicated for variable rows but that is where Mike's solution comes in) then I can't see a problem with picking the file number from row 2. I ran up a quick template for all 4 rows as a single detail selection and can pick anything I want. The File Number field did not naturally appear as the first column in the table since the order of the columns is, by default, the order of the fields defined across row 1 then row 2 and so on. But it was picked and you can re-position the columns to the order you require.

                                           

                                          If you don't always have all (4??) rows for each record that should make little difference to the result of picking the File Number data. However the method of selecting and identifying the fields related to REGULAR, SICK, HOLIDAY, VACATION (Holiday and Vacation are separate?) and so on may require some clever bits along the lines Mike described.

                                           

                                          It may also be possible to make use of the Field Options > Start Field> Preceeding String  feature. But your example post sort of suggests (unless I have misinterpreted) that you have a fairly consistent output (and you have also said you can get the data you want except the File Number field) so that is what I have been considering first.

                                           

                                          Anyway, I will look forward to picking up on this when I get back, though I do suspect you will have your solution by then.

                                           

                                          Best regards,

                                           

                                          Grant

                                           

                                            Originally posted by Peter:

                                          Mike,

                                          I can get the data that I need. I need to have the File Number in each record. The problem is the File number is on the second line. It seems I can only get the File Number or the Payroll info, but not both.

                                           

                                          Peter /b[/quote]

                                           

                                          [size="1"][ August 11, 2003, 06:33 PM: Message edited by: Grant Perkins ][/size]

                                          • Multiple line trap
                                            Peter _

                                            Thanks for all your help. I'll give these suggestions a try.

                                             

                                            Peter