6 Replies Latest reply: May 15, 2014 10:13 AM by spudmcc _ RSS

    Need to exclude data

    spudmcc _

      I am having a terrible time trying to figure out a way to exclude some data.  I have a page header that includes "totals for route" and then multi-column region designed to pickup day of the week and data associated with it.  Problem is that I can't seem to exclude TOTALS FOR DISTRICT area and the same data as the totals for route.  Trouble is that this is creating duplicates because the trap doesn't stop at the end of the individual route and includes the totals for district because it is setup on the form in the same exact way. 

       

      any help would be greatly appreciated. 

       

      andy

       

       

       

      TOTALS FOR ROUTE: 47580166 CIRCLE K 76 (SINGLE COPY TYPE = DSA)                                                                               

      MONDAY                                      TUESDAY                                     WEDNESDAY                 

      DATE   DRAW RETURN ADJUST     NET  %RETURN  DATE   DRAW RETURN ADJUST     NET  %RETURN  DATE   DRAW RETURN ADJUST     NET  %RETURN   

      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


           

      07/02      2      2     0       0    100.0  07/03      2      2     0       0    100.0                                                

            -


      -


      -


      -


      -


             -


      -


      -


      -


      -


                                                       

      AVG        2      2     0       0    100.0  AVG        2      2     0       0    100.0                                                                               

      THURSDAY                                    FRIDAY                                      SATURDAY                  

      DATE   DRAW RETURN ADJUST     NET  %RETURN  DATE   DRAW RETURN ADJUST     NET  %RETURN  DATE   DRAW RETURN ADJUST     NET  %RETURN   

      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


           

      07/05      2      2     0       0    100.0  07/06      2      2     0       0    100.0  07/07      2      2     0       0    100.0    

            -


      -


      -


      -


      -


             -


      -


      -


      -


      -


             -


      -


      -


      -


      -


           

      AVG        2      2     0       0    100.0  AVG        2      2     0       0    100.0  AVG        2      2     0       0    100.0                                                                               

      WALL STREET JOURNAL                                                          

      PRINT DATE: FRI 07/13/2012  9:29 AM                      SINGLE COPY BY DAY                                            WSJDSARET0707  

      PRINTED BY: SY107                                      07/02/2012 - 07/07/2012                                          PAGE       8                                                                               

      AREA: ALLAREAS  REGION: SNGLCOPY  ZONE: SCREG02  DISTRICT: S01                                                                               

      TOTALS FOR DISTRICT: S01                                                                               

      MONDAY                                      TUESDAY                                     WEDNESDAY                  

      DATE   DRAW RETURN ADJUST     NET  %RETURN  DATE   DRAW RETURN ADJUST     NET  %RETURN  DATE   DRAW RETURN ADJUST     NET  %RETURN   

      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


           

      07/02     58     51     0       7     87.9  07/03     38     38     0       0    100.0                                                

            -


      -


      -


      -


      -


             -


      -


      -


      -


      -


                                                       

      AVG       58     51     0       7     87.9  AVG       38     38     0       0    100.0                                                                               

      THURSDAY                                    FRIDAY                                      SATURDAY                  

      DATE   DRAW RETURN ADJUST     NET  %RETURN  DATE   DRAW RETURN ADJUST     NET  %RETURN  DATE   DRAW RETURN ADJUST     NET  %RETURN   

      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


           

      07/05     58     44     0      14     75.9  07/06     58     50     0       8     86.2  07/07     58     46     0      12     79.3    

            -


      -


      -


      -


      -


             -


      -


      -


      -


      -


             -


      -


      -


      -


      -


           

      AVG       58     44     0      14     75.9  AVG       58     50     0       8     86.2  AVG       58     46     0      12     79.3

        • Need to exclude data
          Olly Bond

          Hello Andy,

           

          You can tell Monarch to start and stop the MCR after specified templates, and it's sometimes helpful to create a dummy template that traps just blank lines, and telling the MCR to stop when it finds this.

           

          Another trick that might help, assuming you've got Monarch v10 or above, is to use the Cleared By template option. If you set the Totals for Route template as an Append, and create another Append template for the Totals for District, and set the former to be Cleared By the latter. Then you can filter out the duplicate data in the Table window.

           

          Hope this helps,

           

          Olly

            • Need to exclude data
              spudmcc _

              Not sure how to do the first option.  Tried 2nd option and it didn't bring the data in from column 2 and 3 but is still including stuff from column 1 (which is Monday/Thursday).

                • Need to exclude data
                  Olly Bond

                  Hello Andy,

                   

                  If you drop me an email with your model and report I'll have a bash at it for you.

                   

                  Best wishes,

                   

                  Olly

                    • Need to exclude data
                      spudmcc _

                      It is on its way to you.  Thank you so much for giving it a look.

                        • Need to exclude data
                          Olly Bond

                          Hello Andy,

                           

                          Model built and on its way to you.

                           

                          Your data breaks over pages so you do need to use the Page Header template, but you can handle the MCR by stopping and starting the region on the same TOTALS... Append template, and using this to pick up the TOTALS FOR ROUTE as well as the TOTALS FOR AREA lines, then filtering out to avoid the duplicates.

                           

                          Best wishes,

                           

                          Olly

                            • Need to exclude data
                              spudmcc _

                              Thank you so much for the fantastic and prompt solution to our painful problem.  The model has been a source of angst for the last 6 months or so for one of my team.  She has struggled with the model and finally gave up and each week manipulated the file by hand as best as she could.  This cost the company time and resources we just don't have available not to mention the possibility of errors.  Your solution was complete and accurate allowing us to deploy it immediately.  I personally want to thank you for not only the solution but for helping us understand the issue.  You didn't just create a model for us to close the question.  You helped us see our mistakes and most importantly show us how to utilize Monarch better in the future.  Thank you so very much!

                               

                              Andy

                              Dow Jones/New York Post