UK-based consultant Grant Perkins continues to share his insights with us while I do my best to fight back against a touch of pneumonia.
As users will know (I hope!) Monarch offers a broad selection of Functions to help us extract information from a wide range of data sources and then shape it into the format required for further use in analysis or presentation. The list grows with every release and in recent versions the potential for people to be able to create their own “User Defined Functions” tuned to their particular regular needs has greatly extended the concept and aided productivity.
Some functions we will use pretty much every day, other so rarely we may almost forget about them or, worse, not be fully aware of what they can offer to make our lives easier or enhance our value to those authorizing our income payments!
So I thought a short list of some of my favorite irregularly used functions and features might spur others to suggest list of their own with the intention of providing everyone with a focal point for appreciating how they may gain by using tools with which they may not be familiar or, perhaps, in ways they has not considered.
Let’s start with functions.
TEXTLINE()
TextLine was introduced a few versions back and then quietly enhanced with an additional option that can be very powerful but may not be in everyone’s toolkit.
TextLine allows you to take a block of text derived from a multi-line field and split it into lines by specifying which line number you want. So far so good. The benefit is that Monarch, for database efficiency reasons amongst other things, will tend to ignore the format of the text and create a new concise display of the characters. However, sometimes the original format matters and so TextLine allows us to present individual lines or reconstruct the original text in its original lines.
What may be less well known is that TextLine also has a SEARCH mode. This allows you to show the content of a line if and only if the line contains a search string that you specify as a parameter for the function.
For those readers already familiar with the concept of the ‘Preceding String’ in the advanced field properties the TextLine SEARCH mode is quite similar in use. The obvious difference is that the search string will be embedded in the line rather than preceding it. (For anyone not familiar with the Preceding String feature …. you really should have that one in your tool kit. I encourage you to check it out.)
TextLine Search mode has many uses but perhaps the most common use is for financial type reports where encoded transactions appear as lists but the codes can be in any order. So it’s no good mapping line 1 to the field for the code “AA” since the value contained my not be AA’s value and, indeed, may not even be in the right format. (The latter situation would at least be likely to create an error to let you know there was a problem!).
TextLine with SEARCH mode would allow you to specify that you want the line that contains, for example “AA:” in the first 3 characters (or maybe position 3 to 5, or whatever you can define to make it unique) and so will populate you calculated field only when it finds that line somewhere in the target multi-line field extraction.
This is a very simple example and you can make the function perform much more complex searches by feeding in different variable parameters – say a reference to another field maybe, or a User Entry field provided when the model is run.
Why not take some time to experiment with it for a few minutes so that you can see and memorize its potential for a future model?
The SPLITS
I had been using Monarch for quite a while before something I read helped me to stumble across the real potential for using the SPLIT functions. These functions, variations on the same functionality, can be incredibly powerful and are usually very easy to apply, though some reports may introduce special challenges that require slightly more thought and effort.
NSPLIT is a specific Name Splitter that can be considered to be of rather specific application but the LSPLIT and RSPLIT functions are more widely applicable and provide easy to use facilities for dealing with reports that are not consistent in presentation or any sort of variable content input field that needs to be broken out into separate component parts.
Great tools and in my view a very important part of the Monarch modeler’s armory.
DTOC and CTOD
Or, in extended form, Date to Character and Character to Date conversions.
Once again these tools allow you to work in ways that direct working with a date of character field will not. One obvious use is for enabling lookup activity between sources that present date information in different ways but avoiding the need for extra work to convert one or other source file into the preferred format before making the connection.
A hidden calculated field as part of the model can make the link work for you.
Likewise if you have a specialized character based date input and wish to display in the same character format on output but perform date based calculations – like elapsed working days for example – you can convert to date format, calculate and then convert the result back to your date needs using hidden fields for the calculated part of the work
Being familiar and confident with these functions can make working with Monarch so much more productive.
Compound Filters
It’s usually relatively easy to build and test a simple filter but working on something complex can be a nightmare, and also may make it difficult to check the results.
So don’t.
Create simple filters that you are comfortable with and can be checked easily – and on sequential sets of data extractions if necessary so that you can see the effects as the filter build up one after the other.
When you are happy with the individual filters make a Compound Filter from all of them using the original stand alone results to test the final outcome.
The secondary benefit of this is that you and up with a known good set of single filters ready and waiting to be included in other Compound filters in many different combinations.
Better yet, if any of the single filters need to be changed (and the old version discarded for any future use) you only need to change one filter definition – all the compound filters that use that single filter will be updated.
(That said be sure that you fully document things for the future and if you work in an environment with shared models and multiple modelers you will need to be certain that you have some controls in place for the way the modeling ‘team’ operates …)
Filter by Value
This is a recently introduced feature that offers tremendous potential for specialized filtering work whether as the only filter applied to the data or as an additional filter used on a previously filtered subset of data.
From the modelers point of view it is probably the quickest way to see what values a field holds and of course to identify any and all records that do not have a value for the field.
By using the “Add current Values” button you make dynamic filtering (i.e. the values in that report) a possibility with virtually no effort. By ticking the option that will filter only unmatched values you can seek out anomalies compared to a desired list. Include empty or NULL value rows can help quickly identify records that might contain errors. All within the existing filter parameters of course.
Bear in mind that if you need a quick list of all possible values in a field this may be the way to get it. Un-filter everything, auto-populate from the table and the click in the top left hand corner of the 2 column display box. The box will turn black. Control-C will copy the entire 2 columns to your clipboard. (Sort the second column first if that is useful – just click the bar at the top of the column to flip the sort order.)
I’m sure that we will see a number of excellent uses of this feature as an output benefit presented on the forum as time passes. But for me the potential of Filter by Value as an assistance tool for model development to make life easier for the modeler is what stands out right now.
Summary
We all have our favorite ‘things’ and ways of working and to constrain people to specific approaches is more than likely to stifle creativity and stunt the options for problem solving. However at an individual level, as a Monarch Modeler, having comfortable familiarity with the tools available can make individual tasks that much more satisfying. Favorite tools, falling immediately ‘to hand’ and being usable without having to work out what to do with them each time you pick them up are important in our lives. I would encourage everyone think of the core tools they need for their Monarch activity in these terms. If your needs are similar to mine then the list above may be a good starting place from which to fill the ‘favorite tools’ drawer.
By my calculations, Grant has offered some tremendously valuable ideas. What are your “go-to” functions, features and/or other approaches that you use to excel with Monarch?
