15 Replies Latest reply: May 15, 2014 10:08 AM by Bill Watson RSS

    Suggestions on difficult capture

    Terry007 _

      I have a report that is giving me problems.

       

      The general format is:

       

      1234 ABC

       

        field11   field12...etc

        field21   field22...etc

        (varying number of these)

        -XYZ Total

       

      2345 DEF

       

        field11   field12...etc

        field21   field22...etc

        (varying number of these)

        -XYZ Total

       

      the above is not bad and I've gotten these, however I also pick up the following unwanted fields which only occur once in the report but I can't

      filter them out since I can't seem to find anything uniquely different.

       

      Now right below the -XYZ Total from above we continue with the part I pick up but cannot filter out.

       

         field11  field12 .... etc.

         field21  field22 .... etc

         (now with a variable number of lines)

                       -


      • THIS. TOTAL

       

      then other report stuff I ignore....

       

      So I've tried to create a new template that starts with the -XYZ TOTAL but this has trouble selecting the end.  I was doing this as an append with the hopes that this would be a unique append capture and might allow me to

      remove it from the Table later.

       

      Hope this makes sense.....I'm looking for suggestions.

       

      T

        • Suggestions on difficult capture
          Data Kruncher

          Hi Terry,

           

          I think that I modeled your report successfully. Let's try this.

           

          The field11 lines are the detail template. Use a footer template for the total lines to capture the description of the total.

           

          Yes, this will get you records that you don't want. Sometimes it's unavoidable, at least without significant effort. It's OK.

           

          In the Table window, create a filter using something like this:

          Asc(Left(,1))<>42[/CODE]

           

          42 is the ASCII value for the asterisk, which I hope actually appears in your report.

           

          The effect is that only records whose footer does not include an asterisk as the first character will be included, which is what you're after, I think.

           

          Kruncher

            • Suggestions on difficult capture
              Terry007 _

              I'll give this a try....I've never used a footer before

               

              Thanks

                • Suggestions on difficult capture
                  Olly Bond

                  Hello Terry,

                   

                  I think Kruncher's suggestion is the right way to go, but if your sample data is exactly like the fragment you posted, then your footer might need to use a floating trap on the word "total" in order to pick up both versions that occur.

                   

                  If that's the case, you might need to be a little bit careful as to the sample line you use to define the footer template to make sure you capture the initial * character in the first column.

                   

                  If you get stuck, to help us diagnose it, you can post a report so that it appears exactly laid out as in Monarch by using the and tags (without spaces).

                   

                  Best wishes,

                   

                  Olly

                  • Suggestions on difficult capture
                    Terry007 _

                    Still having an issue

                     

                    as your recall the last group of fields that end with * THIS. TOTAL and begin after the -XYZ TOTAL is what I need to remove.  So when I set up a footer template, I assume I begin with the -XYZ TOTAL as the line to find (of course it finds all such lines and then I'd like it to select the end being the

                    • THIS. TOTAL as the last line.  But I'm having trouble since the End Advanced option does not allow me to pick up this group....I must be doing something wrong?

                     

                    -XYZ TOTAL

                     

                         field11 field12 .... etc.

                         field21 field22 .... etc

                         (now with a variable number of lines)

                                          -


                    • THIS. TOTAL

                     

                      • Suggestions on difficult capture
                        Data Kruncher

                        Using the

                         

                        -xyz Total

                         

                        line as the sample line for the Footer template, use the word total as your trap, starting in position 6. Now turn on the floating trap and paint the description characters left of the word Total. If you really need the word Total included in your data set, you can always add it back in with a calculated field (TotalDesc + " Total") later on.

                          • Suggestions on difficult capture
                            Terry007 _

                            Ok....here is a snipit of the report......still having problems using the footer and float to select the "lines to remove".

                             

                             

                            5102 ABC - xxxxxx ccccuuuuuuuuus

                                                              

                              TERM FGD          27         83

                              ORIG 800         105       1988

                              DB QUERY                      

                              -IXC TOTAL                    

                                                              

                            5119 ABC - XX mmmmuuuiiiiiii    

                                                              

                              TERM FGD          25         37

                              ORIG 800          90         94

                              DB QUERY                      

                              -IXC TOTAL                    

                                                              

                            5792 DEF - DEF  LLLL DDDDDDDD, LLC

                                                              

                              ORIG 800           6        121

                              DB QUERY                      

                              STATE TAX                     

                              -IXC TOTAL                    

                                                              

                              ORIG FGD        2569      14914                   trying to remove

                              TERM FGD         508       2358                   trying to remove

                              ORIG 800        1634       5585                    trying to remove

                                 700             0          0                          trying to remove

                              QUERY CHARGES                                      trying to remove

                              QUERY MSG        526        913                   trying to remove

                              STATE TAXES                                          trying to remove

                                         -


                              -


                            • EXCH. TOTAL     4711      22857                trying to remove

                            /CODE

                              • Suggestions on difficult capture
                                Olly Bond

                                Hello Terry,

                                 

                                A similar approach is to just trap for -IXC in the footer, not using floating, and then define a field which will just be "TOTAL" for "good" records, and null for records beyond the last -IXC. Then a filter of ="TOTAL" will work.

                                 

                                This won't work, however, if your report contains repeated *... TOTAL lines followed by subsequent "good" lines, so the floating trap approach is a better one.

                                 

                                Best wishes,

                                 

                                Olly

                                  • Suggestions on difficult capture
                                    Terry007 _

                                    Ok.....this is great....but i'm just not sure why it worked?

                                     

                                    - the floating footer on "TOTAL" found each line with a total in it and the "* EXCH. TOTAL" was nicely found as expected.  The field "EXCH. " was defined and the detail template found all of the detail lines with those fields defined....but what logic tells the Monarch to tag only the detail lines above the " EXCH. TOTAL" line with "* EXCH. "?

                                     

                                    -Also why did we have to use the Asc(Left(,1))<>42 like compare in the filter instead of an IF with FooterTotal="* EXCH. "?  which by the way did not work using the FooterTotal="* EXCH. ".

                                     

                                    Just trying to understand the footer template more and this filter comparison.

                                     

                                    Thanks

                                      • Suggestions on difficult capture
                                        Data Kruncher

                                        Good questions Terry.

                                         

                                        what logic tells the Monarch to tag only the detail lines above ?[/QUOTE]

                                         

                                        When a footer template has been defined, any data that it captures will be added to the detail records that appear before it.

                                         

                                        So with a typical structure of a group of Detail records followed by a footer such as this:

                                         

                                        Detail record group 1

                                        Detail record group 1

                                        Detail record group 1

                                         

                                        Footer group 1

                                         

                                        Detail record group 2

                                        Detail record group 2

                                        Detail record group 2

                                        Detail record group 2

                                         

                                        Footer group 2[/CODE]

                                         

                                        The footer for group 1 will be associated with the detail records for group 1, and the same goes for group 2. For more information which may help, see [URL="http://********************/extract/understanding-template-types"]Understanding Template Types[/URL].

                                         

                                        why did we have to use the Asc(Left(,1))<>42 like  compare in the filter instead of an IF with FooterTotal="* EXCH. "?[/QUOTE]

                                         

                                        Using asterisks in filter formulas is looking for trouble, as it represents a "wildcard" or literally "anything", so what you're really saying is "match anything".

                                         

                                        To find items that are quite literally an asterisk such as in your case, you've got to resort to the most fundamental value for the asterisk. In this case, the [URL="http://www.asciitable.com"]ASCII[/URL] code that's used to store the computer's representation of an asterisk in memory, the decimal value for which is 42.

                                         

                                        Does that help to clear things up a bit for you?