7 Replies Latest reply: Jun 17, 2015 4:13 AM by Steve Caiels RSS

    Ugly data and the floating trap

    trork _

      Applying a "." trap on the below data works okay, even though it is missing some of the data which seems have slid to the left of the main data area:

       

      I could almost live with it, but the below is really challenging me.  I do not have to use Monarch (v.11) very often, but when I need it, I really need it.  Any advice on a trap that might capture this type of floating data?  I have tried a floating "Acc" trap, but it does not pick up every line, maybe because the Acc falls right smack in the middle of the amount before which I need to extract, and the code following it which I need to extract.

       

      Thanks,

      tarork

        • Re: Ugly data and the floating trap
          MelaSarenas _

          Hi Tarork,

           

          Add more traps.  Maybe something like  ßAccß   What lines are not being captured by your Acc floating trap?

           

          Regards.

          Mela

            • Re: Ugly data and the floating trap
              trork _

              Thanks, Mela.  I'm just getting back to this project.  My game plan is to add a separate trap for each Acc code that we need.  Such as a floating trap for "Acc   23", "Acc   29".  Hopefully that works.  Does anyone know if there is a limit on the number of append traps?  I have about 30 codes for which I need the amount.

               

              Theresa

            • Re: Ugly data and the floating trap
              Steve Caiels

              Hi Tarork,

              I’m not sure that that approach will work.  You only have 20 Appends available, but in any case, they will append data to a single detail row rather than create rows in the table.


              I think we will be able to come up with a solution.  Are you able to provide a sample report along with an Excel sheet showing what you need to extract?


              Regards,

              Steve.

                • Re: Ugly data and the floating trap
                  trork _

                  I created a text from the PDF, with SSNs removed for purposes of sharing this.  I still need to capture the SSNs, but that has been easier than the Acc amounts.  The jpg shows what is needed as far as those pesky Acc codes.  Basically, I need the ACC code number, plus the dollar amount to the left of the Acc code.

                   

                  My first attempt at this I just captured all Acc codes and amounts, and then eliminated what is not needed.  But if I have to capture them one at a time, then I’ll limit it to these codes.  It’s not as many as I thought, and only 2 over the limit.

                   

                  23, 26, 29, 32, 35, 38, 41, 44, 47, 50, 53, 56, 59, 62, 65, 68, 71, 74, 80, 81, 82, 83

                   

                   

                  Thanks for any help.  I’m licensed for v. 11, but working on upgrading to v. 12.  I used a trial and didn’t find it all that difficult once I got used to the layout.

                   

                   

                  Theresa A. Rork

                • Re: Ugly data and the floating trap
                  Steve Caiels

                  Hi Theresa,

                  From the screen shot, I can’t see why it is picking up the lines in the first image, but not the 2nd image – assuming you are using the floating trap of course.
                  I would have thought that the trap suggested by Mela above each of the two columns should work.  It will also extract data from the accumulations, which I guess you don’t need. 

                  To eliminate theses, you could extract an append template on the “Accumulations to date” line.  Highlighting this header text as a field would allow you to filter those rows out in the table.

                  Regards,
                  Steve.

                    • Re: Ugly data and the floating trap
                      trork _

                      I tried the trap Mela suggested and I get these results.  It is floating, but doesn’t pick up anything.

                      cid:image002.jpg@01D0A839.1B1C34E0

                      I also tried this floating trap, but where “Acc 23” appears in a different location, the field does not adjust with the trap. I thought it was supposed to do that.

                      cid:image006.jpg@01D0A839.1B1C34E0

                       

                      Working in Monarch v. 11 at this time and the original PDF of this document which I found easier to work with than the .txt.  I just can’t share the PDF because of the SSNs.  I do need the accumulations also, or at least the ones which match the specific Acc codes I noted.

                       

                      --theresa

                    • Re: Ugly data and the floating trap
                      Steve Caiels

                      Hi Theresa,

                       

                      The floating trap won’t adjust the starting position of a field before the trap.

                       

                      Let’s say that the highlight for the field in your sample line starts on 30 and is 10 characters long and the trap follows in column 40, for example.

                       

                      The default extraction logic for this field will be:  Start highlighting the field on column 30 and stop after 10 characters or when you bump into the floating trap – whichever happens first.

                      If the trap was in column 70, the field would start in 30 and end in 39.  It won’t float up to the trap position.  This is what is happening on your 2nd screen shot.

                       

                      But if the trap was in column 15, the floating trap would stop it before it had chance to start on column 30 and nothing is highlighted.  This is what is happening on your first screen shot. Note the last digit of the 219.19 is highlighted where the field just has time to get going before the trap stops it.

                       

                      However, there are lots of other issues that are going to complicate this extraction.

                      It’s a messy report for sure and I think we will struggle to resolve all the issues on the forum.  I’m pretty confident that Datawatch or one of our partners will be able to build a model for you, but we’d need to have sample reports.

                       

                      If you want to continue to work on it, then here are a few ideas:

                       

                      Extend the start position of the field in your existing sample to accommodate the highlighted data in the report preview.  In this case, you might get untidy data back if you are forced to extend the field too much.  You can fix this using calculated fields in the table.

                       

                      The extraction is further complicate by the fact that you have a different number of ACC traps on different lines.  Two for the detailed part of the report and 6 for the accumulations.  It could probably be done with some fairly hefty calculated fields in the table, but it might be best to have one model for the bulk of the report and another for the accumulations.

                       

                      To handle the lines with two ACC elements, you’d need to trap on each ACC

                      Highlighting all the data before, in-between and after these two ACC traps will give you an untidy version of the data.  You’ll need to increase the template width on the field properties for the field in-between the two ACC traps.  For example:
                      “Block Federal Tax                12,345.67”,”25 COMP   9,876.54”,”39 COMP”
                      Calculated fields could tidy this up to:
                      “12,345.67”,”25”,”9,876.54”,”39”

                       

                      Now you will end up with two (or more) sets of data on each row. You can export these as a fixed width text file, then bring them back in to Monarch using the Multi Column feature and get one data set per row.
                      “12,345.67”,”23”
                      “9,876.54”,”39”

                      Regards,
                      Steve.