Monarch lets you do this - from about v6 and above - using the MultiColumnRegion.
In the Report window, select the first line of detail (John). Don 't create a template yet, but in the Templates menu, set the MCR to be active, with 3 columns, starting in position 6 with a width of 3. Back in Template > New Template, define your detail template and make sure the tickbox for MCR is active. You only need to define two fields - "John" and "5". Now define an Append template with MCR active so that A B and C are picked up.
You should have the data you want in the Table window. MCR is amazingly powerful and let you handle all sorts of tricky problems. Give me a shout if you'd like any help with it.
Olly, this is intriguing. Thank you for your help and for pointing MCRs out. I had never really used them before, but I suspect I will definitely be playing around with them in the future.
I may have oversimplified the data that I'm dealing with though. Here is a censored version below. Is it possible to have multiple columns of varying widths? Or irregularly spaced? The data that I'm working with doesn't have any headers, but I'm going to add them in just to point out what I'd like to trap. And if it simplifies the process, I think I can edit the original data source to provide headers for use in an append template (if necessary).
AA000001 006 Y 1990 001 1A 0 06 00000 Y N N 5 00078 00066 5 00044 00040 4 00012 00012 0 00000 00000 0 00000 00000 2 006 008 2 010 012 0 000 000 0 000 000 00000 000 000 0 000 000
9 071692 000.00 000000 01 100947 Y 531 0 000 0 0 0 00 0 0 0 F150 F 001 M M 061
AA000002 008 Y 2000 001 0 05 00000 N N 0 00000 00000 0 00000 00000 0 00000 00000 4 00030 00030 4 00034 00034 0 000 000 0 000 000 0 000 000 0 000 000 00000 000 000 0 000 000
9 071692 000.00 000000 00 000000 N 531 0 000 0 0 0 00 0 0 0 TRAILR T 800 209 /code
I only want information from records that start with AA00000X, so the 9- records are irrelevant. This data represents the effect of a proposed rate change on a list of policies. The Od# series is the old premium, and the Nw# series is the new premium. Unfortunately there is extraneous information in between these series, and the field widths are not uniform.
What I would like to eventually turn this into is data that looks like this:
AA000001 1 00078 00066
AA000001 2 00044 00040
AA000001 3 00012 00012
AA000001 4 00000 00000
AA000001 5 00000 00000
AA000001 6 006 008
AA000001 7 010 012
AA000001 8 000 000
AA000001 9 000 000
AA000001 W 000 000
AA000001 X 000 000
AA000001 Y 000 000
AA000002 1 00000 00000
By using a MCR like you suggested (with a column width of 14), I can grab the first five columns of the Od# series. But beginning at Od6 I start to run into problems. The field width for the Od# series prior to Od6 is five, but it needs to switch to three thereafter. Moreover, the column width needs to switch from 14 to 10. This would grab the next four of the Od# series, and then I would need to reposition again for the final two. (I am also interested in the Nw# series. But I believe/hope that, once the Od# series is successfully catpured, the Nw# series will fall into line without much trouble.)
Can this be done?
Thanks again for your help.
Yes, you can define columns that can cope with variable widths, and it helps that in your case it looks like all the columns in each row are populated.
The trick is to define the MCR with the right number of columns (24? in your case, bearing in mind Monarch's limit of 40), each with a column width of 1, and with the left hand position of the first column way over in the right hand margin of your report (e.g. position 300 or somesuch). Then define your data as one big field, which I usually call , and define a calculated field :
to return the data you want from each record.
This is really slick.
So am I understanding this correctly?
A Multicolumn region doesn't actually need to include anything within it from the report. If I defined a MCR with 5 columns at the right margin of my report, I would effectively be multiplying each record by five? Each group of five records would be indexed by a column number from the MCR, and I could do various operations on whatever detail information I do pull in based on which column() value the record has?
Thanks And yes, you're understanding it right. I gave an outline of it (for fixing variable width columns in PDF reports) in a webinar for Datawatch a year or so ago:
Subject: Monarch Tips & Techniques #5 : Invisible Data
Recording URL: https://www117.livemeeting.com/cc/datawatch/view[/url]
Recording ID: QPC3BW-1
There's a lot more to it than just odd-width columns - for example, you might want to connect data with date ranges to data with specific dates. So table A has got entries that indicate a condition applied on a range of dates like:
01.01.2012 - 29.02.2012
and table B has got that something that says that an event happened on a particular date:
There's no way a conventional external lookup from B to A will get you a join in this sort of scenario - you need to blow up table A so that you have one record for every date in the range, and this technique, iterated twice, lets you do that.
Enjoy the holidays,