+ Reply to Thread
Results 1 to 5 of 5

Thread: Monarch filters

  1. #1
    dentalexpert is offline Junior Member Monarch Forum Newcomer (Welcome!)
    Join Date
    Jun 2008
    Location
    Ireland
    Posts
    3

    Default Monarch filters

    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.

  2. #2
    Data Kruncher is offline Monarch Forum Distinguished Fellow Emeritus Perpetual Guru of Monarch
    Join Date
    Sep 2002
    Location
    Vancouver, Canada
    Posts
    1,705

    Question

    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
    Maximize your Monarch skills in 30 days with this training package.
    ---
    Now supporting Monarch V10.5 Pro and V9.01 Pro.
    Help us help you. Post a sample of your problem between the [code] and [/code] tags and preview your post.

  3. #3
    Joey is offline Senior Member Monarch Black Belt 2nd Dan
    Join Date
    Dec 2005
    Location
    USA
    Posts
    317

    Default

    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.

  4. #4
    dentalexpert is offline Junior Member Monarch Forum Newcomer (Welcome!)
    Join Date
    Jun 2008
    Location
    Ireland
    Posts
    3

    Default Monarch filter

    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

  5. #5
    Data Kruncher is offline Monarch Forum Distinguished Fellow Emeritus Perpetual Guru of Monarch
    Join Date
    Sep 2002
    Location
    Vancouver, Canada
    Posts
    1,705

    Default

    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.
    Last edited by Data Kruncher; June 25th, 2008 at 11:10 AM. Reason: Near simultaneous posting
    Maximize your Monarch skills in 30 days with this training package.
    ---
    Now supporting Monarch V10.5 Pro and V9.01 Pro.
    Help us help you. Post a sample of your problem between the [code] and [/code] tags and preview your post.

+ Reply to Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts