Er, yeess. Good challenge.
The constraint seems to be the single line detail record which, in effect, constrains the Append to a single line as well.
I'm thinking along the lines of re-formatting the report prior to processing but even that is not problem free.
For example even to re-format the spearated line into a single line it would be necessary to always have at least 2 lines available as the sample for the template. So either 2 lines as per the posted example or one line and a blank line.
In fact everything I can think of at the moemnt requires that 2 lianes are available.
So then I'm thinking EITHER extract the Market Area code as a multi-line field AND the rest of the line as another multi-line field (based on a single detail template probably trapped somehow on the Market Area field) and then reformat or extend the width of the Market Are field, control the appearance of the other 'field' to maintain the rows format and then export that to create a revised input file OR;
Use the first line of the 'rest of the record' for a sort of reversed lookup where the re-constituted Market Area code is added to the original report from an external look up and re-exported thus giving a revised version of the report to work with.
There may be some variations on the theme depending on the data content and whether that can be used in some way that is not obvious from the de-personalised data.
The first option seems more logical as there is only one step BUT I'm not sure how easy it would be to control the starts and ends (and so the published export format) of the 'rest of the line' field so that it looks as it did originally.
Lost trailing spaces may be a problem?
I'll try to get back to this later ...
Thanks for the input Grant. (Edit: Nick, you posted while I was typing. Will the approach below work for you too?)
I've come up with a resolution to the problem, thinking along the lines of Nick's brute force solution to Ralphs SetRuntimeParameter problem yesterday (nice one, BTW) - come at it another way.
I made a model to extract only the Market Areas, ending on Left Justification. I then summarized this and exported it to an xls file.
Then I changed my original model to stop treating the Market Area as an append, and include it in the detail.
A scan of the report (which included all Market Area names which will ever appear) revealed, quite fortunately, that all of the bbbbbbb items were unique and would therefore only ever apply to their associated aaaa items. So...
I then edited the exported xls to add new rows for the bbbbbbb and aaaaa items, filling in the full Market Area names, and simply duplicating the one line names in the other column, as necessary.
Finally, I added an external lookup to translate the one line Market Area field (be it aaaa or bbbb) to its full equivalent.
Nice that the bbb items were unique. Were the aaa items that had associated bbb items also unique as combinations? (I was not sure from my rather swift reading of your description.)
If so you could base the entire solution on a fixed (internal?) lookup table using just the aaa item (as an append even) as the key.
Which seems to be pretty much what you did.
Sometimes I guess we have to rely on the data content helping the solution along!
On the other hand does this problem maybe suggest an addition to the wish list? The potential for an append to have a number of lines in its 'sample' which are not constrained by a detail template but only by the number of lines OR another occurrence of itself?
You're correct Grant, it was fortunate that the aaaa's were also unique.
You're also correct that this could all be handled with an internal lookup table. I just found it easier to edit externally.
The other nicety in this otherwise troublesome piece was that the report does a page break on every new Market Area, so it never shows more than one on a page.
This would indeed be a nice enhancement. v9.02?
I fully agree about the external edit.
... but then you could cut and paste the result into an internal lookup and (thinking V9) have it as an object to be linked to from other models - should you ever need them.
A little easier perhaps than linking to an external lookup since the table is not dynamic.
Just thinking aloud and trying to bring life to some of the V9 possibilities.
I'm trying to use an internal lookup, but I can't get it to ignore the second line. Here's how my report is formatted:
JOBNAME IS HERE 5187X 170244.42 170146.50 170146.50 97.92 97.92 0.00 0.00 W
5606X 857.12 857.12 857.12 0.00 0.00 0.00 0.00 W
8742 6307.68 6307.68 6307.68 0.00 0.00 110.40 0.00 W
8810 15025.78 15025.78 15025.78 0.00 0.00 238.93 0.00 W
JOB TOTALS 213513.11 213387.29 213387.29 125.82 125.82 349.33 0.00 W /font[/quote]And here is what the table is showing:
JOBNAME -NOT FOUND- 5187X 170244.42 170146.5 97.92 0[/font][/quote]I was also going to do another lookup that would display the Job Number based on the name. The only problem is that some Job Numbers have the same Job Name!
If I can get the trap to ignore the second line, I can make it work.
If you are adopting DK's 2 step process I would see the first step as the 'First Column' - Job Number and Job name.
Is that always 2 rows?
If so make the sample for this simple detail extraction a 2 row sample and you should have little problem with trapping of line 2 since it would be covered by the first trap's template. (I think, based on what you posted.)
Does that make the lookup table (internal or external) any easier to work with or have I missed something else?
Or are you talking about the second step detail record where a field or append (to may the connecting key) would be difficult because there is no obvious way to stop the second line being trapped? (I think you may well be looking at that difficulty rather than my first one.)
I'm fairly new to this forum, although I've been using Monarch for many years. I've faced similar situations in the past. Here's what I've done (it requires a bit of programming):
I take the input file, and create a new input file by adding either at the beginning or at the end of each line the absolute line count relative to the top of the file. I end up with a file that looks like this 300-line sample file:
1 xxxxxx xx/xx/xx 9999.99
2 xxxxxx xx/xx/xx 9999.99
300 xxxxxx xx/xx/xx 9999.99[/font][/quote]I then create two separate models: one to capture the detail, including a field for the absolute line number, and a second one to capture the append (in this case, of course, as a detail record), also with the absolute line number as one of the fields. Once I have the two exports, I simply join the two resulting tables on the absolute line number.
At some point, I tried to use the built-in Line and Page functions, but when you have a multi-line field, the functions return the page and line number of the last line; if they returned the page and line number of the first line, then I wouldn't have to create my own line numbers. Perhaps this option should be considered as a possible future enhancement.
Are you using the programming to add the line number?
It occurs to me this could also be done using Monarch (for those like me who "don't" program ).
Simply select the entire line of the report as a single field. Add the line number as a calculated field using the Line() function and then export (or print to a disk file) the result as a fixed width type file.
Then run the 2 model process.
Still looks a bit tricky though unless the report ALWAYS allows the template for the split line data to consist of 2 lines, even if the second line is empty.
Or am I overcomplicating this?
Thanks for the suggestion. There is just one twist: the function to use should be Recno(), which returns the unique record number, instead of Line(), which returns to line number relative to the top of the page. Otherwise, it works great.
David /b[/quote]David, thanks for pointing that out - I should have remembered that Page() needed to part of it as well.
The only downside with RECNO(), though it will not apply in this case I guess, is that it relates to the record in the table rather than the line of the report. You probably know that anyway but it may be worth making the point for those who read this weeks or months from now. Of course if one is picking every line anyway that relationship will be one to one so no problem.
Sounds like it has been a useful discussion for both of us, which is good.