From time to time you may run into a report designed by some renegade programmer who thought that having values shift positions from line to line on the report was actually a good idea. Additionally, maybe she thought that, rather than display zero values, it would be best if the report simply doesn’t display anything at all for that field.When it comes to making it easy for the Monarch modeler, nothing could be further from the truth. We like predictable (in that we know that a value – something! – will appear where it should) and nicely aligned values.
When the Floating Trap Isn’t Enough
Sometimes you can handle these situations nicely with the floating trap feature. But sometimes there just too many problems with the line for Monarch to make sense out of it.
Fear not! All is not lost. But it will take a little extra work to get the results you need.
Recently a user of Datawatch’s Monarch Forum posted this snippet from his report:
OPEN BILLED RCPTS ADJS REFS CHANGE XFERS XFERS XFERS CLOSE 0-30 31-60 61-90 91-120 121-150 151-180 TURN OVER
BC TOTAL
549428.75 424468.29 -173614.33 -300985.92 0.00 -11869.87 -44360.03 0.00 0.00 443066.89 321047.56 39894.32 34844.97 -32766.67 -179.92 80226.63 -0
MCR TOTAL
4356874.49 3887773.75 -1447498.42 -2542241.09 0.00 0.00 -130712.26 0.00 0.00 4124196.47 3185408.90 260237.40 154257.40 157188.27 175734.26 191370.24 -0
WC TOTAL
64420.13 -19328.27 -14944.49 0.00 30147.37 18500.13 11647.24 0
GRAND TOTAL
13174622.19 6984031.36 -2854893.59 -4196472.77 12526.20 -7605.05
Hmm. This sample doesn’t post very well here. Rather than spending my time fighting with it, I’ll point out the original post with the sample.
You can see that there are both floating and missing values in the sample. The best of both worlds, as it were…
The solution involves knowing how many fields you expect to see on the line, and introducing a device – an unusual character, anything that you wouldn’t expect to normally see in the report – to allow for extracting the values easily with one of Monarch useful functions. This character will allow us to split up the fields later on.
The first step is to capture the entire line as a single field, rather than even trying to get this field and that field at the outset. We’ll name that field “A” as it really doesn’t matter what we call it.
Next, we insert our splitting character. Field B has the expression:
In field A, any occurance of 11 consecutive spaces will be replaced with a single vertical bar, otherwise known as a pipe character. Why 11 spaces? Any time there are 11 spaces in this sample, we know that a needed field is completely missing. You’ll need to carefully assess your particular report to determine this number.
Next, we clean things up a little bit more. We’ll still have a number of repeated spaces, so determining exactly where our fields are is still a bit of a challenge. Now we eliminate the repeated space, replacing them with just a single space. Field C has the simple expression:
Now it’s a simple matter to replace all of those unwanted spaces with the splitting device (our delimiter). Field D has the expression:
Be a Data Chef: Slice and Dice!
Finally we have string with nothing but values and pipe characters. Monarch can dissect values from such a string easily. Our first field that we really want from the report, Open, is a Numeric field and has the expression:
Monarch will split field D into 40 sections, based on the location of the pipe characters. You can always supply a higher number of sections than you really need. Monarch doesn’t care. Just make sure that you’ve supplied enough. The exact number is fine if you want to count the fields.
Once Monarch extracts that bit of the original field, it’s still a character string, so we use the Val() function to convert it to a number, as that’s expected by the field type we defined.
The next field, NetBilled, simply increases the last value in the last formula by one, like this:
By spending a few minutes creating the remaining fields (use the Duplicate button), you’ll have tamed this beast of a report that you thought was not possible to model with Monarch.
The process could be streamlined a fair bit by combining the separate calculated fields. Instead of using the various fields B, C, and D as presented above, you could establish field B as:
Then your final fields would use:
Additionally, by introducing just a couple more fields, you could make this a portable solution for any report that is giving you the same problem, and the only work you need to do to model for that particular report would be to define the final values fields themselves.
By using runtime parameter calculated fields for the number of spaces (SpaceCount), the delimiter character (NewDelimiter), and maybe the number of fields if you really want to be picky, the final expression would be:
Alternatively, Let Excel Split The Fields
Building the various numeric fields that split our B field (with all of the pipe characters), might be avoidable. And you can achieve with the same end result too!
Just export the B field, and any other fields you’ve appended, to an Excel file or text file, then import that file into Excel and use Excel’s Text to Columns feature, specifying the pipe character as the Other delimiter. Excel will put each field value in its own cell properly across the columns.
Now that’s using Excel with Monarch!
Powerful Data Mining Technology for Everyone
It’s easy to take your tools for granted, especially after you’ve been using those tools for awhile. But every time I see a really oddly designed report, and someone has created a Monarch model for it, I’m reminded of just how good Monarch really is.
Combining many of Monarch’s features, and perhaps using them in unusual or even possibly an unintended manner, will really let you excel with Monarch.
