The ability of Monarch to sort key fields in both the Table and Summary windows is tremendously useful as it enables us to further analyze our data. The ability to focus only on the most important data can be critical when working with data sets of any size.
Let’s assume that we have a list of 36 records reflecting banking transactions. We want to create a list showing only the largest value each transaction at each of the branches. In our data set, there are 12 transactions at each of three branches. Once the entire data is filtered, we expect to see only three records: one for each branch number, with the largest transaction amount that occurred for that branch displayed.
Monarch makes it easy to sort records on multiple key fields. In fact, within a single Monarch model, one can define multiple manners in which the data will be sorted at any given time as is demonstrated with the Sort Orders dialog box.
Users of Monarch v10 and later have an advantage over users with earlier Monarch versions when filtering data that employs a defined sort order, as they can build filters that take the active sort order into consideration.
By utilizing the Advanced properties of the filter definition, we can specify that we need to handle duplicate values for a particular field carefully.
By activating first the custom sort order and then the new filter Monarch will list only three records in our sample data set, as expected.
Notice that the option for duplicated rows reads “first row only, as sorted”. That means that we’re effectively restricted to only a single row when duplicates are encountered, as is the case with our 12 records per branch.
Note that we’ve selected the “Unique Rows” option in addition to the “Duplicated rows” option. By using this option we ensure that we always have a representative record for every branch location. This would be necessary if in our set of 36 records we had a distribution of 20 records for one branch, 15 records in another and only one record for the final branch.
Top N Records
What would we do if we wanted not just a summary of our data but rather a complete list of all of the fields available for the top, say, 10 records for a given key field? Monarch doesn’t make it easy (in that an option display such data readily doesn’t exist) but for Monarch Pro users it is possible.
The goal of this challenge is to filter the Table window to display only a particular number of records for every key item value. Employing summaries are effective for drill-up and drill-down solutions, but not for listing the top n detail records for all key values. We’ll need to build a filter in the Table window to satisfy this requirement.
First, build and apply the custom sort order as above. Next, add a new numeric calculated field named SortRowNo using only the expression Rowno(). We’ll use this field as a key part of the solution. Astute readers will be quick to point out that filters cannot be built when any component of the filter employs the Rowno() function. Monarch will complain loudly.
Instead, assuming that this is a process that would be repeated regularly, you’d define a project export to export the unfiltered Table window to a temporary file (Excel workbook, Access database table, or what have you, as applicable). Save the model and project files, and run the project export.
Now in a new Monarch session, open the freshly exported file as a database source. Import all of the fields. Now that the SortRowNo contains only values, and not the Rowno() function, we can use those values in a filter. But we need one more value before we create that filter.
What we’ll do next is a little odd. We’re going to create a new external lookup. The data source for the lookup will be exactly the same file that we just opened, and the key field will be, in our case, the branch identifier. Monarch will complain about the specified field not forming a unique key to the external table. That’s OK. Click the “Yes” button and continue to import the SortRowNo field as LookupRowNo.
When the external lookup is performed, all equivalent branch identifiers will be assigned the same value for LookupRowNo: the row of the Table of the first model in which that branch first appeared.
Now with a complete data set, we can define a filter which will result in the top 10 records that we’re after. For the filter use the expression:
LookupRowNo – SortRowNo < 10
Build in Flexibility
By replacing the fixed value of 10 in the filter formula with a new runtime parameter calculated field (named rtTopN for example), we can make the model much more user-friendly and flexible. Upon opening the model, Monarch will prompt the user for a value to be used in the filter.
Additionally, values for runtime parameters can be passed to a model via a batch file to make the process even smoother. Imagine a business process that monitored the top 10 records each week, but only the top three items were required for monthly reporting. A batch file could be created and copied with only a slight edit in the copy to change the value for the rtTopN amount. Use the /set command line parameter to assign a value to a runtime parameter calculated field.
Ignore the Restrictions
This approach demonstrates that we can sometimes circumvent the technical restrictions that Monarch imposes to build solutions that resolve the challenges that exist in your information systems, whether they’re large or small.
Earn a spot in your organization’s Top 10 list. Excel with Monarch.
