On day 10 of the 30 Days to Become a Better Monarch Modeler series, we’ll put Monarch’s functions to work to improve our models, both by facilitating finicky data extractions and by adding useful calculations that are based on the extracted data itself.

On day 8, I wrote about the types of calculated fields. In the Table window, functions can only be used in formula-based calculated fields, filters, user-defined functions, and in the Find dialog’s expression builder.

Monarch supplies us with a good number of built-in functions. So much so in fact that in order to better locate them, Monarch presents them by category. At the top right of the formula tab dialog we can change the default from “All Functions” to one of the six available groups:

1. Date: everything related to calculations with dates
2. String: everything related to calculations with text (why didn’t they call it text?),
3. Numeric: everything related to calculations with numbers,
4. Conversion: everything related to changing from one data type to another,
5. Special: a handful of logical and verification functions,
6. Report: functions which return data about the report itself, and
7. User-defined:

I should mention that the term string is often used by programmers to refer to text, and to be consistent with Monarch’s terminology I’ll use it here too.

There’s no practical way to discuss the purpose and proper syntax for each and every function here; there are just too many of them. Besides, I’m not certain that doing that would be of any value as Datawatch has done a fantastic job of documenting each function within the formula tab. As a function is selected, the online help is displayed at the bottom of the dialog.

So what’s my job here today? I suppose that it’s to reinforce the need to understand what data type a function will return. Many if not most of the difficulties that I’ve seen Monarch students struggle with when creating calculated fields is not understanding why Monarch is giving them an error; most often the all too brief “Operand Types” message.

The “Operand Types” message is telling us that we’re mixing data types within the formula, such as attempting to divide a string value by an integer. The root cause is often not understanding the type of data that the function used in the formula will return.

Functions to Assist in Modeling

Some of the functions used to often assist in capturing data that is difficult to isolate in a template alone, for whatever reason, include:

• LSplit and RSplit, which break up long strings into pieces based on your instruction
• Extract, which seeks out particular strings or characters within a longer string
• Left, Right, and SubStr which extract text from the left side, right side and middle of strings
• InTrim, which cleans up duplicate spaces within a string, and
• The indispensible TextLine which, as the description indicates, can be a life-saver by isolating a particular line within a block of text.

Functions to Improve Captured Data

Even when you are able to capture data with a template alone, the results aren’t always optimal when they’ll be used for other systems or calculations. When that’s the case, some of the go-to functions include:

• CtoD, which converts Characters to Dates,
• Str, which converts numbers to strings,
• Val, which converts strings to numbers, and
• Chr, specifically Chr(13), which represents a carriage return character and acts like the Enter key on your keyboard.

You might be wondering about that last one. Sometimes a report might have address data scattered on the report and you might have to paint the fields separately instead of within a single multi-line field. Add to that the complexity that data entry isn’t always perfect, and suddenly you’ve got, say, State and Country data appearing in the wrong place. By combining separate fields while using a “hard” return with Chr(13), as in State+Chr(13)+Country, you can then use that calculated field as the input for an Address Block (which we’ll discuss later) to have Monarch do the data cleaning for you.

Your Task for Today

Nobody expects you to memorize every available function in Monarch. But what you can do is begin to examine the functions. Just a couple every day will probably do. In a model that you store just for testing and experimentation purposes, build calculated fields using the functions of the day. In very little time, you’ll get familiar with them.

Adding Functions Makes for Less Work

Using the right function in the right role will vastly reduce the time that it takes to build accurate models, and the quality of the model too. Instead of fighting with particular trapping methods, and potentially re-inventing the wheel to derive a calculated value, put functions to work for you, and you’ll excel with Monarch.

—

Continue your commitment to Become a Better Monarch Modeler with Part 11 of the series, or review Part 9.