10 Replies Latest reply: Jan 6, 2015 9:15 AM by Gerard Kinsella RSS

    Trap line only with text above

    Gerard Kinsella

      Hi I am trying to trap a subtotal line and ignore a group total line. The subtotal line is distinguished by having text above, unlike the group total line that has a blank line above, the line attrributes are the same however so I'm unsure how to trap -

       

      Example from report, I've marked the lines I need:

       

      13                 08 XXXXXXX                  1.0000       13.33         13.33 

         ACCOUNTING    AA.AAAAAA              585.77                          < Need this subtotal line        

                                                                                      

      13                 08 XXXXXXX                  1.0000       44.86         44.86 

         ACCOUNTING    AA.AAAAAA               44.86                         < Need this subtotal line         

                                                                                      

      13                 09 XXXXXXX                  5.0000         .34          1.70 

         ACCOUNTING    AA.AAAAAA                1.70                          < Need this subtotal line        

                                                                                      

         ACCOUNTING    BB.BBBBBB            11006.84                      < IGNORE this total line           

       

      Could someone help me please with this? I am new to Monarch and would appreciate the assistance.

       

      Kind regards

        • Re: Trap line only with text above
          DataExploiter _

          Hi Gerard Since Europe is about 2 hours from the new year I’m sure Olly Bond cant respond. Let me give it a try as a user but no expert. Try and make your detail trap TWO lines. So trap 13                08 XXXXXXX                  1.0000      44.86        44.86 but also include the line below in the detail trap. Therefore include the subtotal ACCOUNTING    AA.AAAAAA              585.7 as part of the detail trap as an example Hope that helps . Best John

            • Re: Trap line only with text above
              Gerard Kinsella

              Thanks for the response John.

               

              That didn't work, if I am understanding you correctly that is?

               

              The amount of lines above the sub-totals are variable and could have a hunded lines and then sub-total, then the next group and so on, until finally grand totalling.

               

              Using your method (which I had previously tried), I was unable to get a consistent approach to grabbing the sub-totals and ignoring the grand total - it was getting most sub-totals, but not all for example.

                • Re: Trap line only with text above
                  DataExploiter _

                  Try trapping the subtotal as a FOOTER template. ACCOUNTING    AA.AAAAAA              44.86      Since it’s a footer template and the grand total comes after the subtotal it should not append to the detail records above the footer subtotal trap. I cant imagine there is not a simple solution. John   

                  • Re: Trap line only with text above
                    Eamon Donoghue

                    Hi Gerard,

                    Like Mo, I'm a little unsure about the requirements given the small sample.  I'm assuming that the only data you need from this report is contained in the Sub-total lines and that this is in effect your Detail line.  Am I correct in this assumption?   If this is so, you should be able to capture just the Sub-total lines using a two-line Detail template as follows: (Image pasted below)

                     

                    Select the line above the Sub-total line and the Sub-total line itself.

                    Trap on some pattern in the top line - say two numerics at the start of the line.   It doesn't matter once it creates an unambiguous trap.

                    Select the fields you need on the second line (the Sub-total line).

                     

                    Save your template

                    Verify your tempate - always a very important step!

                     

                    In the Table window, you should see the data you require.  This should exclude the Grand Total line as it will not satisfy the trap condition.

                     

                    Please forgive me if I've misunderstood your requirements.  But if I read your spec. correctly, this should work.

                     

                    Best regards,

                    Eamon.

                     

                     

                     

                     

                     

                     

                • Re: Trap line only with text above
                  Mo Abdolrahim

                  Dear Gerard,

                  Create a detail template with three lines, and make the third line as the trap line.

                  Define the subtotal field on the third line, and another field on the first line.  On the first line, I called the field name Flag, and defined it as a character field.  In the Table design, create a filter where the Flag field is not empty (flag<>"").

                  Apply the filter and you end up with three rows that contain the subtotal fields:

                  The above model is done in Modeler v12.

                  Regards

                  Mo

                    • Re: Trap line only with text above
                      DataExploiter _

                      Mo Just a thought, what about Gerard’s comment “The amount of lines above the sub-totals are variable and could have a hunded lines and then sub-total, then the next group and so on, until finally grand totaling." How can you trap all the details if your detail trap is on the third line containing the subtototal. Most of detail based on Gerard’s comment does not have an immediate subtotal located on the third line. It works based on his sample but not with “hunded lines and then sub-total”?? Would my approach work. Capture the details on a one line detail trap and then capture the subtotal separately as a footer template. Since the grand total is below the subtotal and footer template trap. The grand total would be ignored because it’s below the footer trap and its associated details. Is this correct?? Thanks John

                        • Re: Trap line only with text above
                          Mo Abdolrahim

                          John,

                          It would be better if the sample data contained all possible scenarios so I could come up with a better traps. Based on the example that Gerard provided, both the sub-total lines and grand total line are identical.  I made an assumption that the third line above the sub-total line has data, and there is no data on the third line above the grand total.  He needs to inspect lines above the sub-total line (or the grand total line) and find some data that is unique and does not exist above the grand total line, and use it as filter.

                          As far as your second suggestions goes I am not sure if it works.  I would suggest to create a test case and test it.

                          Regards

                          Mo

                            • Re: Trap line only with text above
                              DataExploiter _

                              Mo, Gerard has a solution along with multiple ways to trap. Using my last approach, capturing the subtotal as a footer template works. It ignores the grand total and will capture all the details, multiple details lines with one associated subtotal. The first approach like your response captures the subtotal but only one line of detail immediately above the subtotal. My first solution does not need a filter to remove the grand total. So using my second approach (footer trap for subtotal) will capture all the details with the associated subtotal without capturing the grand total. Mo, thanks for having me test, it was helpful to see if it worked. Regards John

                        • Re: Trap line only with text above
                          Olly Bond

                          Hello everyone,

                           

                          There's a downside to using multi-line templates which is that page breaks might interfere, giving you 2 or 4 lines instead of 3 between the detail and the subtotal. There's a workaround for that using "preceding string" in the advanced field options, if you always have "Accounting" in the subtotal line. Define a 2 line sample, trapping on line 1 using the detail, and in line 2, select a field that lines up vertically with the subtotal data you need. Edit this field's properties so that it is selected when "accounting" occurs as a preceding string.

                           

                          A filter for non-null subtotal values should then give you the correct results.

                           

                          Best wishes,

                           

                          Olly

                            • Re: Trap line only with text above
                              Gerard Kinsella

                              Thanks ever so much for all the responses - it is greatly apprecaited!

                               

                              The two line trap was looking to have been working perfectly - but as Olly suggests I noticed that whena  subtoal followed a page break it could treat it as a grand total as it had a null field preceding it.

                               

                              I want to get your suggestion working Olly, but due to my in exeperience I don't quite get you sorry.

                               

                              From original example below -

                               

                              13                 08 XXXXXXX                  1.0000       13.33         13.33 

                                 ACCOUNTING    AA.AAAAAA              585.77                          < Need this subtotal line        

                                                                                                              

                              13                 08 XXXXXXX                  1.0000       44.86         44.86 

                                 ACCOUNTING    AA.AAAAAA               44.86                         < Need this subtotal line         

                                                                                                              

                              13                 09 XXXXXXX                  5.0000         .34          1.70 

                                 ACCOUNTING    AA.AAAAAA                1.70                          < Need this subtotal line        

                                                                                                              

                                 ACCOUNTING    BB.BBBBBB            11006.84                      < IGNORE this total line           

                               

                              I am selecting two lines and on line one trapping by saying if the line has ACCOUNTING in the relevant position - when I move to line 2 I am unsure which field to apply the preceding field rule to (or which field in the Advanced tab should I use)? I'm using Monarch 11.

                               

                              Sorry for taking up your time.

                               

                              Kind regards