9 Replies Latest reply: May 15, 2014 9:56 AM by Steve Caiels RSS

    Show last record of group

    abbarition _

      Hello everyone! Long-time reader, first-time poster. I should take a moment to say thanks to everyone who has ever posted. I have learned a lot from all of you!

       

      Now on to my current problem. If this has been mentioned here before, I apologize, but I don't remember seeing anything that would have answered this problem. I have a report where I need to pull a Branch#, Branch Name, ATM#, and Amount and export it into Access. There can be multiple ATM's at each Branch, and multiple amounts for each ATM, but I really only need to see the last amount for each ATM at each Branch. I don't think I can only choose the last amount in my template because the page header can fall in between the last amount and any criteria I could use to identify it as the last amount.

       

      I know I can easily choose every amount in my template but can I use a filter to show only the last amount for each ATM? As an alternative, I can use a calculated field to show the record number and use a GroupBy query in Access to show only the last amount, but I would prefer not to pull extra records into Access if I don't need to.

       

      Any suggestions?

      Thanks!

        • Show last record of group
          Grant Perkins

          Hi,

           

          First some questions.

           

          Which version of Monarch?

           

          Do the records in your file by any chance have a date (and time?) stamp?

           

          If the answer to the date/time question is yes, then you might be able to use that data as a selection criterion. If not I assume the report will be in a logical order for you requirement so I think you could use ROWNO() (or maybe RECNO() depending on circumstances - they work differently) in conjunction with the MAX function using the 'Matching' attribute in the summary field to provide a summary by ATM that only gives the entry with the highest selected value. For instance the highest row number.

           

          Would work, as far as I can see, in V6 and V7 but I have only checked it in V7 using some data that I think will provide the same results.

           

          Hope this helps.

           

          Grant

           

           

          Originally posted by abbarition:

          Hello everyone! Long-time reader, first-time poster. I should take a moment to say thanks to everyone who has ever posted. I have learned a lot from all of you!

           

          Now on to my current problem. If this has been mentioned here before, I apologize, but I don't remember seeing anything that would have answered this problem. I have a report where I need to pull a Branch#, Branch Name, ATM#, and Amount and export it into Access. There can be multiple ATM's at each Branch, and multiple amounts for each ATM, but I really only need to see the last amount for each ATM at each Branch. I don't think I can only choose the last amount in my template because the page header can fall in between the last amount and any criteria I could use to identify it as the last amount.

           

          I know I can easily choose every amount in my template but can I use a filter to show only the last amount for each ATM? As an alternative, I can use a calculated field to show the record number and use a GroupBy query in Access to show only the last amount, but I would prefer not to pull extra records into Access if I don't need to.

           

          Any suggestions?

          Thanks! [/b][/quote]

          • Show last record of group
            abbarition _

            I apologize, I forgot the first rule of posting...always list your version! I just started using Monarch 7 this week (a jump up from our Monarch 5!). There is no date stamp for the records but they are in order, so I'm using the RcdNo function. I received an error when trying to use the RowNo in the Summary but that could have easily been because I was trying to use it incorrectly.

             

            I had tried using a Summary, but I am new to Summaries and am so unfamiliar with all the settings. I have the Branch#, Branch Name, and ATM# set as the Key fields and I have the Max(RcdNo) set in the Measures section. But I don't know how to match the amount. If I put Amt in the Item section, it shows every amt. If I put Amt in the Measure section, I have to use an aggregation of the Amt. I don’t think I can use an aggregation when I just want to show an actual amt from one record. Is that correct?

             

            Am I even getting close, or have I gone completely in the wrong direction?

            Thanks!

            • Show last record of group
              Grant Perkins

              Ah, you have been missing the joy of summaries all this time!

               

              OK, further to my previous suggestion, now I come to look at it again I see that my previous test case does not match your need. I have not yet found an alternative that works as a single process.

               

              So plan B is required for an immediate workable solution (Although there may be an alternative approach from the original report. For my analysis I created a file that assumes that each data line has Branch number, Branch Name, ATM number and then the Value field. If your original report is of a different format there might be other options.)

               

              So ....

               

              Trap and model the data (already done). Add in a calculated field for Line Number.

               

              Create a summary with key fields Branch Number, Branch name, ATM number and Value (Amount). Set the Measure to Line Number.

               

              Set the properties of the Amount field to be sorted by Line Number - Descending.

               

              Set the DISPLAY properties of the Branch Number, Branch Name and ATM number fields to 'suppress duplicates'.

               

              You will still have all of the lines but the last line recorded will be at the top of the rows for each ATM and will be clearly identifiable because the information for the other three fields will appear to its left.

               

              Export the summary table to a fixed width format print file.

               

              Using the new 'report', create a model that traps ONLY the lines where Branch and ATM details are populated. That should give you the numbers you need.

               

              Variations on the concept are possible of course.

               

              Hope this helps. If I find something better I will let you know but at least this should provide a solution for now.

               

               

              Grant

               

               

              Originally posted by abbarition:

              I apologize, I forgot the first rule of posting...always list your version! I just started using Monarch 7 this week (a jump up from our Monarch 5!). There is no date stamp for the records but they are in order, so I'm using the RcdNo function. I received an error when trying to use the RowNo in the Summary but that could have easily been because I was trying to use it incorrectly.

               

              I had tried using a Summary, but I am new to Summaries and am so unfamiliar with all the settings. I have the Branch#, Branch Name, and ATM# set as the Key fields and I have the Max(RcdNo) set in the Measures section. But I don't know how to match the amount. If I put Amt in the Item section, it shows every amt. If I put Amt in the Measure section, I have to use an aggregation of the Amt. I don’t think I can use an aggregation when I just want to show an actual amt from one record. Is that correct?

               

              Am I even getting close, or have I gone completely in the wrong direction?

              Thanks! [/b][/quote]

              • Show last record of group
                abbarition _

                I really have been missing out on summaries! It's not the sort of information we needed in the past, but it looks like I might need more of this type of information in the future. I will be sure to take the time to learn more about them!

                 

                Your suggestion works perfectly. The only thing that would be better would be to have it all in one report & model, of course, but that’s just being picky! Thanks so much for all your help!

                • Show last record of group
                  Grant Perkins

                  That's a reasonable desire.

                   

                  You could get a long way to a single click process by creating a batch file script that runs the 2 parts for you.

                   

                  Just a thought.

                   

                  Sometimes I like to see what is happening at each stage incase a previously unseen facet of the data has appeared! If you know what I mean.

                   

                   

                  Grant

                   

                  Originally posted by abbarition:

                    The only thing that would be better would be to have it all in one report & model, of course, but that’s just being picky! Thanks so much for all your help! [/b][/quote]

                  • Show last record of group
                    abbarition _

                    I hadn't thought about running a script, which is odd because I have been writing quite a few of those lately. This is a very simple report that would actually be ideal for a script because I know what to expect every time it is run. There are no surprises in this report (which is unheard of for most of our reports).

                     

                    By the way, just wondering, why can't you export the Monarch summary to an Access table as is? For example, I am suppressing the duplicate ATM#'s so it only shows the first ATM# in the summary. But if I try to export the summary to an Access table, it show the ATM# in every record of the table. Is this a Monarch limitation or am I missing an option?

                     

                    Thanks again for all your help!

                    • Show last record of group
                      Grant Perkins

                      Not sure how you could export to Access without popultaing all the fields in every record.

                       

                      The suppression facility in the summaries is excellent for on sceen work or even export to a report print or text file. But I can't see how it would help with a database where a fully populated table would be required. Wouldn't it?

                       

                      Grant

                       

                      Originally posted by abbarition:

                      I hadn't thought about running a script, which is odd because I have been writing quite a few of those lately. This is a very simple report that would actually be ideal for a script because I know what to expect every time it is run. There are no surprises in this report (which is unheard of for most of our reports).

                       

                      By the way, just wondering, why can't you export the Monarch summary to an Access table as is? For example, I am suppressing the duplicate ATM#'s so it only shows the first ATM# in the summary. But if I try to export the summary to an Access table, it show the ATM# in every record of the table. Is this a Monarch limitation or am I missing an option?

                       

                      Thanks again for all your help! [/b][/quote]

                      • Show last record of group
                        abbarition _

                        Normally, you would want a fully populated table. But I was thinking in this case, if I could export with the blank fields, I could have one Monarch step (instead of two). Then when I write my Access query, I could just have it show only records with ATM numbers.

                        • Show last record of group
                          Steve Caiels

                          Hi,

                           

                          I can’t think of a way of getting it in one pass, but another approach would be the following

                           

                          a)     Create a calculated field using recno() and reverse sort on it.

                           

                          b)     Export the table to a data format (Excel for example)

                           

                          c)     Close All in Monarch and re-open the database exported in b)

                           

                          d)     Create a filter.  Give it a name, but do not enter a filter expression.  Click on the advance tab and show only records that are unique with respect to ATM.

                           

                          I’ve tested that with the Music sample files and it gives the last item shipped per customer.

                           

                          Regards

                          Steve