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.

[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

[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.

[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

[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.