4 Replies Latest reply: May 15, 2014 10:01 AM by Data Kruncher RSS

    Monarch filters

    dentalexpert _

      hi

      am a new user of monarch pro and I am wondering if its possible to build a filter to extract a list of records based on the maximum value in a column in the table. This value also exists in the orginal report.

      For example say there is a table with the following numbers

      8038

      8048

      8058

       

      Each number will have many thousands of records associated with it. I want the filter to extract all the values associated with the highest number in this case 8058.

        • Monarch filters
          Data Kruncher

          Hello and welcome to the forum.

           

          I believe that how we derive a solution will depend upon which edition of Monarch you're using. Is it the Standard or the Pro edition?

           

          Kruncher

          • Monarch filters
            joey

            Yes, it's possible.  There are several ways you could do this, and it depends on your use of the model.  I'm going to assume you want to completely automate everything.

             

            I would do this with two models and runs.

             

            In the first model, add a calculated field KEY that is a character field.  The formula should be "KEY".  Then add a summary.  the KEY field is your key field and the mesaure is MAX(NumbericColumn). Export the summary to FirstRun.xls  Save your model and project.

             

             

            The second model takes your report as input.  Add another calculated field KEY with the same value.  Then add an external lookup to FirstRun.xls, using KEY as the common key and importing Max(NumericCOlumn).  The filter of the second pass would be ImportedMax(NumericColumn)=NumericColumn.  You can hide the KEY field and use Monarch as you like in the second model.

             

            Other options that I know involve changing the filter manually based on the summary in the first pass, or using Access/Excel instead of the summary/external lookup trick.

              • Monarch filters
                dentalexpert _

                thanks guys for taking the time to reply

                 

                Data Krucncher - I am using version 9.01 monarch pro

                 

                Joey - I will try out your suggestion,

                 

                Sincerely

                dentalexpert

                • Monarch filters
                  Data Kruncher

                  OK, well, this is exactly where I was going, right down to the "Key" field. Joey, I swear that you were looking over my shoulder as I was typing a draft response in Notepad. Have you planted a webcam in my office?

                   

                  The only thing I would add is that this can all be done in one model/project. You just need to refresh the lookups after exporting the summary, if you're doing it manually.

                   

                  Of course, all of this is still contingent upon dentalexpert having Monarch Pro. Edit: OK, that's been answered now.