Over the past week, we’ve been discussing Monarch various functions that you can use in your calculated fields and filters. So far, we’ve covered date functions, the special report functions that return data related to the origin of the data (as opposed to the data within the report), and we even had a quick look at the set of special functions that Monarch offers to check data values, generate random numbers (often used in auditing) and its implementation of the “if then else” type of function seen in many software packages.

Accurate Data: The Final Frontier

It’s tough enough sometimes just getting the data we need properly captured from within our reports and other source. But sometimes our data isn’t always exactly what we want it to be, at least in terms of type, and since some manipulations are easier to perform than one type than another, today we’ll examine the functions that allow us to convert from one data type to another.

Asc()

Every single character, number and symbol that you can type in a document and view on your computer (and some that you can’t see too), are stored internally by the computer as values. “Asc” is short for ASCII, an acronym for what essentially is referred to as the standard way that your computer converts characters to values to store and use in its memory. There are countless web pages (even entire sites) that can inform you about the ASCII values (or “ASCII table”), but one of the cleaner, more concise ones available just happens to be on the Datawatch support site.

One additional benefit of the table on the Datawatch page is that at the bottom you’ll find how all of this relates to the Monarch Utility program, and may well be the only official documentation on how to perform search and replace tasks with the Utility.

So what’s the big deal with converting letters and symbols and such into a decimal number, and what would you ever possibly need this for with Monarch? Have you ever seen odd square boxes, or symbols that don’t seem to belong, in a data file? With the Asc() function you can determine exactly what that thing is, and deal with it properly yourself, or tell the supplier of the data file exactly what bad data is being inserted incorrectly into your report.

CharToSeconds()

This function converts a string that looks like a time, like “10:09:36″ into the number of seconds since midnight, a numeric value.

Chr()

Chr() is the opposite of the Asc() function. Where Asc(”A”) returns 65, Chr(65) returns “A”. Chr() is great for inserting symbols like a carriage return: Chr(13).

CtoD()

This indispensable gem has been discussed on this site more than once already, to convert a Character field to a Date field.

DateToJulian()

This function converts a regular date field to a Julian date value. Some ERP systems built with Sybase or JD Edwards software seem to use Julian dates, or variants of them.

DtoC()

Opposites are at work again, this time reversing the CtoD() function, and returning a character string that looks like a date.

JulianToDate()

Anyone care guess what this does?

SecondsToChar()

Yet another opposite function, this time converting the number of seconds from midnight into a character string that looks like a proper time.

Str()

This function converts a numeric value into a character string. The function has a few optional arguments, which allow you to specify how long you want the string to be, the number of decimal place to round the value, and a character with which to optionally fill up any remaining space in the string.

Val()

The final function of this set is also the final opposite; this time converting a character string that looks like a number into a true numeric value.

Universal Translator

I often refer to Monarch, when introducing it to those unfamiliar with it, as a type of universal data translator; able to almost effortlessly convert data from one format or location to another. These functions really do extend the analogy, easily changing and translating raw data from one presentation into another. Use them wisely and you’ll live long and prosper, or at least excel with Monarch.