15 Replies Latest reply: May 15, 2014 10:04 AM by Grant Perkins RSS

    New to Monarch - Double Extraction??

    psrs0810 _

      I have an extremely large report that I need to extract specific information.

      Is there a way to trap the info needed by a double trap (or something like that)

       

      the info I need are current revenue in and current revenue out totals.  but the problem is throughout the report there are a lot of current rev in and out totals that I do not need.

       

      The ones that I need to access are listed under Service Total.

      Also they are not always on the same line under Service total.

       

      Here is a sample:

      SERV TOTAL  - CAR    

      CURREVI A      4,976,229.65

                  B                 

      YTDREVI A     26,816,865.29

                  B                 

      CURFRQI A            34,796

                  B                 

      YTDFRQI A           204,099

                  B                 

      CURREVO A          3,448.08

       

      Thanks for your help

        • New to Monarch - Double Extraction??
          Data Kruncher

          Hi! Welcome to the forum.

           

          Before we get off in the wrong direction, can you please answer a couple of questions?

           

          Which specific items do you want to include, and which do you need to exclude?

           

          And are the labels for the totals consistent throughout the report?

          CURREVI

          YTDREVI

          CURFRQI

          YTDFRQI

          CURREVO

          /LISTAnd finally, do you service line totals always begin with "SERV TOTAL - "?

           

          From your description I'm guessing that you only need CURREVI and CURREVO, but guessing is a bad idea around here.

           

          Thanks,

          Kruncher

            • New to Monarch - Double Extraction??
              psrs0810 _

              First, thanks for responding

               

              I would like to include SERV TOTAL - , CURREVI, CURREVO and their totals

               

              yes, the labels are consistant through out the report.  But there are thousands of CURREVI and CURREVO totals I do not need.  I only need the ones listed below a SERV TOTAL -

               

              You final question - yes they always start with SERV TOTAL - LLL (and a 3 letter character)

               

               

              Also, keep in mind that below SERV TOTAL - the information is not always consistant.  Meaning, CURREVI will not alway be on line 2 follow SERV TOTAL.

                • New to Monarch - Double Extraction??
                  psrs0810 _

                  No one has any thoughts?

                    • New to Monarch - Double Extraction??
                      Grant Perkins

                      Hmm, good bump. This one had slipped off the radar.

                       

                      Do you have at least 2 lines to play with on the report after each SERV TOTAL - line and before whatever the next record might be?

                       

                      If you do, try this.

                       

                      We will make a somple trap, use some slightly advanced and very useful data field selection features and then maybe a filter or two should the need arise.

                       

                      Create a DETAIL template and trap using the SERV TOTAL - text.

                       

                      Paint a field on line one for the type - e.g. CAR.

                       

                      On the next line paint another field where the CURREVI type code appears across the line. Name it to you choice and set the field type, etc. OPen up the field's ADVANCED Properties, Set the START FIELD ON value to 'Preceding String' (on the current line) of 'currevi' (no quote marks.)

                       

                      Do the same for the value field.

                       

                      On the next line repeat the above for the CURREVO fields.

                       

                      Save the model. See what you get.

                       

                      If you get some service totals you don't want you can filter to include ONLY the ones you DO want - which will likely be easier than trying to create a trap of some sort that does that job.

                       

                      HTH.

                       

                      Let us know how you get on.

                       

                       

                      Grant

                        • New to Monarch - Double Extraction??
                          Olly Bond

                          Hello psrs0810,

                           

                          If you have Monarch v10, you could trap the SERV TOTAL lines as an append, as long as there's another append you can find that is present for the detail lines you don't want. Then use the "turn append off" feature, and a simple filter will get you just the data you want.

                           

                          Best wishes,

                           

                          Olly

                            • New to Monarch - Double Extraction??
                              psrs0810 _

                              OllyInMunich - I am working with V9

                               

                              Grant Perkins - When I do the advanced properties I am getting either the CURREVI is not posting and have a null value or CURREVI does show but the total posted is not the correct amount.  it is posting the previous CURREVI total found and if there is not CURREVI for on of the Serv Total is repeats the last pulled Total.

                               

                              Let me know if I need to provide more info to get this fixed.

                               

                              Thanks

                                • New to Monarch - Double Extraction??
                                  Grant Perkins

                                  OllyInMunich - I am working with V9

                                   

                                  Grant Perkins - When I do the advanced properties I am getting either the CURREVI is not posting and have a null value or CURREVI does show but the total posted is not the correct amount. it is posting the previous CURREVI total found and if there is not CURREVI for on of the Serv Total is repeats the last pulled Total.

                                   

                                  Let me know if I need to provide more info to get this fixed.

                                   

                                  Thanks[/quote]

                                   

                                  That sounds like you have the CURREVI value field in an APPEND template. Everything should be in a single DETAIL template as far as I can see looking at the sample data posted PROVIDING you ALWAYS have 3 lines to play with for each Serv Total 'block'.

                                   

                                  (An append template could work but wouold require if you do not have 3 lines but you would need to make sure that its number of sample liones and the trap used are the same as for the detail template.)

                                   

                                  HTH.

                                   

                                   

                                  Grant

                                    • New to Monarch - Double Extraction??
                                      psrs0810 _

                                      If I make the first trap with Serv Total as Detail the next trap does not give me the option for detail.

                                      What am I doing wrong?

                                        • New to Monarch - Double Extraction??
                                          Grant Perkins

                                          If I make the first trap with Serv Total as Detail the next trap does not give me the option for detail.

                                          What am I doing wrong?[/quote]

                                           

                                          Nothing in terms of not being able to create a second detail template.

                                           

                                          Only one Detail trap is allowed and as far as I can see only template is needed PROVIDED that template can always have at least 3 lines of the report included in the sample. (The sample data does not matter much in this case but having 3 lines in the sample does.)

                                           

                                          As posted use

                                           

                                          SERV TOTAL - CAR

                                          CURREVI A 4,976,229.65

                                          B

                                           

                                           

                                          as the 3 sample lines - set the cursor next to the SERV TOTAL to the left to highlight the line and then drag down until three lines are highlighted.  Now add the template.

                                           

                                          If you already have a detail template in the model, select it, then highlight these lines and then promote the lines to be the new sample using the button that looks like a document with an up arrow. (It's to the left of the row of icons related to creating traps in templates.)

                                           

                                          The sample will change and be ready for the steps I described before. [EMAIL="DON@T"]DON'T[/EMAIL] WORRY ABOUT THE DATA IN THE SAMPLE - it's only there for guidance for field positioning.

                                           

                                          HTH.

                                           

                                           

                                          Grant

                                            • New to Monarch - Double Extraction??
                                              psrs0810 _

                                              First - is there a way I can screen print what I am doing and post it on here to show what I am doing (wrong)?

                                               

                                              I have the 3 lines highlighted and my trap is set to pull the serv total.

                                              Then I have highlighted the entire row of "CURREVI A Dollar amount"

                                              if I do the advance part you mentioned it unhighlights the CURREVI row and leaves it blank in the table. 

                                              If I do not do the advance part it pulls every serv total and anything that is on the second line. 

                                              So I am getting all, if not, most of the CURREVI info, but the ones that do not have CURREVI is it pulling what ever is on the second line.

                                               

                                              The more diffucult one will be the CURREVO.  That one is not consistant to it placement under Serv Total

                                               

                                              Thanks

                                                • New to Monarch - Double Extraction??
                                                  Grant Perkins

                                                  First - is there a way I can screen print what I am doing and post it on here to show what I am doing (wrong)?

                                                   

                                                  I have the 3 lines highlighted and my trap is set to pull the serv total.

                                                  Then I have highlighted the entire row of "CURREVI A Dollar amount"

                                                  if I do the advance part you mentioned it unhighlights the CURREVI row and leaves it blank in the table.

                                                  If I do not do the advance part it pulls every serv total and anything that is on the second line.

                                                  So I am getting all, if not, most of the CURREVI info, but the ones that do not have CURREVI is it pulling what ever is on the second line.

                                                   

                                                  The more diffucult one will be the CURREVO. That one is not consistant to it placement under Serv Total

                                                   

                                                  Thanks[/quote]

                                                   

                                                  You don't want the whole row - the preceding string setting will not be matched. Start the field after the string CURREVI some way accross the line - wherever the actual values are likely to start and for as long as it needs to be - probably about 17 chars to include any formatting and the possibility of a minus sign. As long as you get the horizontal position for the field in the right place with CURREVI to its left it should work.

                                                   

                                                  Using the preceding string means that Monarch does not really care which line the field appears on under the first line - or whether it appears at all.

                                                   

                                                  CURREVO should be fine with that. Same concept for its field definition, just change the preceding string to suit.

                                                   

                                                  HTH.

                                                   

                                                   

                                                   

                                                  Grant