As we’re at the midpoint of the 30 Days to Become a Better Monarch Modeler series, today on day 15 I have a real-life challenge and proposed solution that I think quite nicely reinforces some of the concepts and tools that we’ve discussed so far.
This week a new Monarch Forum member named MikeMetta posted a sample multi-column address list and asked for the forum’s assistance in creating a model to handle the extraction properly, as he wasn’t yet achieving the results that he needed. It was, in Mike’s own words, “tricky”.
Looking at his sample it was clear that Monarch, using its regular address handling facilities would have a tough time with it, due to the now-you-see-it-now-you-don’t nature of some descriptive comments that appeared within the addresses. If that weren’t enough, telephone numbers and web site addresses were tacked onto the address without any blank lines in between that information and the address.
What to do?
Instead of trying to extract different pieces with detail and append templates as was being discussed as possible approaches, I began working on capturing the entire block as a single field, with the intention of breaking it up by using a few different calculations myself, and by letting Monarch do some of the heavy lifting too.
The method successfully extracted the sample data properly, so I thought that it might be interesting to share this with you, as this type of challenge, or variations of it, isn’t as unusual and in fact is probably pretty common.
Setting it up
First up was defining the multi-column region which was pretty straight-forward work for a two column layout. On to the detail template…
Looking at the sample, the largest block was six lines long. I created a detail template that was six lines long to handle this painting the single field across the width of the column, trapping on a single alpha character at the start of the field (a non-blank trap would work too, and would probably be better for numbered companies). In the Advanced tab of the field definition, I set the End Field On the end of left justification. To keep it easy for future formula building, I named the field simply as “A”, and that’s the end of the template building. On to the slice and dice work…
Breaking it down
The following employs functions that have already been cited in this series of posts.
In the Table window, deriving the Company name needed a Character field with the formula:
TextLine(A,1)
Now the real data separations start. In order to isolate the true address data, I thought it necessary to separate each component of the block, so line 2 of the block became the field named L2 with the formula:
TextLine(A,2)
Similarly, L3 and L4 with built with TextLine(A,3)and TextLine(A,4).
Now we’ve got the name and address bits broken out, but how do we get Monarch to know which lines are truly address related, and which are descriptive text? The unfortunate answer is that we can’t. At least, I can’t determine a universal solution to that. Still, where there’s a will there’s a way.
I added a user-edited calculated field to the model named Skip. Skip represents the number of lines to ignore in the address block after the company name, since all of the sample address showed descriptive lines, when they existed, between the company name and the address.
Then I keyed into the model a value of zero if no descriptive text existed for that record, or a value of one or two as applicable when descriptive text did exist, as appropriate.
With this prep work done, I could finally build a field that I could use to feed a Monarch Address Block. My AddBlock character field got this formula:
Company + Chr(13) + Chr(10) + If(Skip=0,L2 + Chr(13) + Chr(10) + L3, If(Skip=1,L3 + Chr(13) + Chr(10), If(Skip=2,L4,"")))
Using AddBlock as the data source for an Address Block, I instructed Monarch to extract Address line 2 as “Address”, City as City, Region as State, and Postal Code as Zip Code (for US addresses in the sample). There’s the heavy lifting done as Monarch automatically populated those tough to extract bits into the table.
Finally, the built the Telephone field with:
LSplit(RSplit(A,2,"Telephone: ",1),2,"URL:",1)
and the URL field with:
RSplit(A,2,"URL: ",1)
Your Task for Today
With a reasonably quick effort, I’d developed a single reusable model that was able to extract all of the important data. That said, can you see a shortfall in this model as has been described? What component isn’t clearly (and entirely) isolated?
Your task today may be tougher than the previous tasks, but you’re now equipped to complete it. Add the functionality to the model that I didn’t describe: duplicate this model and to it add the full descriptive text that is integrated in the original address chunk, if any.
Mission Accomplished
While I certainly wouldn’t expect that you’d want to use an approach that employed user-edited fields when hundreds of addresses are to be extracted, but for reasonably small samples it could be a workable long-term solution. It really shouldn’t take too long to populate the Skip field for even a fairly long list of addresses, and given the end result, that time spent doing manual data entry might be viewed as a good investment.
Admittedly we’ve had some bumps in the road, but I hope that you stay tuned for the second half of the 30 Days to Become a Better Monarch modeler, and discover even more that you can do to excel with Monarch.
—
Continue your commitment to Become a Better Monarch Modeler with Part 16 of the series, or review Part 14.
