From your description the multiline field sounds like the right way to go and then use a calculated field to convert ALL of the captured fields, whether with or without the V, to the field you will use.
Im guessing the the result of the multiline approach might be along the line of;
Short field: abc123def456
'Long' field: V abc123def456ghi789klm
In which case you could do with a calcualted field that uses the Short field format but puts it into a longer field or strips the " V " from the beginning of the 'Long' field and displays what is left.
Does this sound about right? If so tell us what you get in the 'Long' field when you use multiline and we can come up with a formula to fix it.
The way the "V" data prints out is:
otherdata otherdata V otherdata otherdata
otherdata otherdata targetd otherdata otherdata
I tried a multi-line, but the 'target data' (what I am trying to extract, sometimes starts directly beneath the end of another field (as in the illust. above, it starts below the "ta" of the field before it. I can't get a clean field that contains only the "V" and my data without getting data from other fields.
It sounds like there is no pattern that allows an easy method of spotting the extra line/field start point unless visibility of a full sample file would show something useful.
Is there always a line between two records which is sometimes empty and sometimes filled with the longer varieties of the field?
If there is then perhaps line will either be populated or not, in whcih case just grab it as a field and use if in place of the "v" field by way of a calcualted field using an IF() formula.
But I suspect the line appears only when required based on your description.
One solution may be to extract the whole line as a multiline field.
That would give you something like;
All on one line.
You might then be able to use the LSPLIT and/or RSPLIT functions to break that single field into multiple fields.
(If using Version 8 the TEXTLINE function could be used to identify when a second line existed.)
This sounds more and more like a rather specific sort of problem which requires its own tailored solution based on the report detail. If the report is not data sensitive is there any chance of posting a representative sample on the forum?
If it is sensitive, any chance of posting a 'cleansed' or disguised version of it?
In my experience of similar things it is always useful to be able to see what the culprit looks like!
The data in question is sometimes 1 line, sometimes 2, and there's always 1 blank line after the data. I tried a 2-line trap, but sometimes fields overlap one another (but never in the same record). A multi-line trap doesn't appear to be appropriate. Here's a sample showing two fields in two records dropping to a second line. Notice how the '2' of the first record's field beginning '280' overlaps the '0' of the second's '7766.00' field. And the same '2' is directly under the '5' of the preceding field (1192.85) which I believe precludes a two-line trap.
CLOSING LINE 48120.94 32268.33 V 1091017.94 9766.36- 2458.00
Good grief. How extraordinary. Or have I led a very sheltered career and missed hundreds of reports written like this?
Can you ever have 2 or more "v" interventions for the same line?
Are the possible occurences of the "V"'d columns limited to certain columns or could they appear anywhere on the line? (I assume not the text at the start?)
I will start to play around with some ideas on the basis that you don't get that.
Ok, here we go.
Taking you sample lines I have assumed that the "CLOSING LINE" does not need to be captured to a field (but if it does it is not a problem really) and that it will make a good trap.
Select a CLOSING LINE row as the template sample. Trap on CLOSING LINE and then paint a field for the entire remaining width of the line to include all possible columns.
Go to the table.
Add a calculated NUMERIC field for 'Column1' (which I assume never has a "v" for this example) with the following formula.
[font="courier"]val(lsplit(intrim(),2," ",1))[/font][/quote]NOTE that I have used the Version 8 INTRIM function. Earlier versions of Monarch can be manipulated in the same way but it gets a bit messier.
INTRIM removes multiple consecutive spaces
LSPLIT splits the field based on the position of spaces. See the function assistance for a full explanation.
VAL converts the CHARACTERS resulting to a numeric field value.
Now lets build on that for the other fields.
,val(lsplit(intrim(),4," ",3)))[/font][/quote]An IF() formula. It checks for the third column position returning a CHARACTER result of V. If it does we take the RSPLIT route to get whatever is at the end of the field - our data from the second line.
If there is no V it LSPLITS the row into 4 sections using space as the separator and picks the 3rd section for the field.
You could also use the TEXTLINE function to replace RSPLIT in this example but there seems little advantage.
The INTRIM function did not seem to remove all trailing spaces as expected which means the RSPLIT formula has to make allowance for one trailing space. That could be a factor of my sample file.
I used 8.02 for this development.
The "v" may not be a "v". It could be a 'down arrow' character. In which case the idea will still work but the formula will obviously need a little adaptation!
Thank you for the suggestion. I will work on it. You'll probably never encounter something like this anywhere but my company. The logic dates back to the 70's, and forces a data field to the next line if the line to its right exceeds its allotted length. This means that no two consecutive fields drop to the next line (clever, I think). Also, yes, the first data field (after the string 'closing line' may drop, but is unlikely to; also, it's not used in this particular model.
So here we are dealing with vintage (or that veteran?) code! Is there no end to Monarch's talents?
The formula can, obviously, be adapted to any field.
Should there be two (or more) fields which drop a line in the same record the idea would need to be adapted to identify which 'real' value we should be looking for. A little more complex but not impossible.
Good luck with the report. Be sure to let us know if you run into any problems.
PS - sounds like the rightmost 'real' column can be ignored in terms of the checks for the "v".
Yes, there can be more than 1 field on the first line with a V. Also, the V's are not always in the same position in a field: "if there's not enough room for 2 spaces followed by a V followed by 2 more spaces, the program just moves a V," according to the PA who analyzed the assembler code.
I tried out your suggestions and, thank you, they work, but only, of course, with 1 V. I could analyze the line before trimming it to determine the position (or relative position) of all the V's, and then I would know which of the 15 fields would be on the second line. More could could use this information to figure out how many strings to count from the right. But....since not all 15 fields would be dropped (yes, the right-most will never drop), I decided to create a number of models strung together in a batch job to feed a database:
1-pick up first line data, skipping the V's
2 through ??--pick up each field's data under the V using a floating trap.
Not very elegant but it beats counting all those spaces.
Finally, I referred to 'assembler' above. The program was written about 35 years ago, and my COO, then a programmer, came up with the idea--to save a line or two per total on green-bar paper when the report printed only 132 columns. It became 160 cols. when we went to fiche, and now it's in COLD so we may just eliminate the V-logic, but he has a sentimental attachment to it. Geesh...talk about legacy.
Ah, I can see your problems and how they may arrive on several fronts!
Would I be correct to assume that the report has columns of a known width and that the decimal places align under all circumstances?
The sample seems to show the "v" one place before the decimal point - is that something like the case and if so is it consistent?
If it is and if any fields displaced to the second line are also consistently placed (as they seem to be) then identifying the field on the second line by an absolute reference to the start position and for a known length should be possible. That might offer a better solution than multiple models.
The columns are of known width and I have each field's starting position (right-side) and length. Decimal places align. The V's, however, may not align: for cosmetic purposes, if there's not enough room for 2 spaces + a V + 2 spaces, a V is placed. Any field dropped to the second line has an absolute starting position, and no problem with overlapping data (because no 2 consecutive fields will have dropped).
Further work using your Intrim suggestion provided me with part of the solution, but working with many nested IF statements is problematic. Can IF statements be arranged like this: "If(condition 1, answer a),If(condition 2, answer b)". I am trying to get away from nesting them and having to enter the 'else' value.
Another possible solution (a bit shorter) requires me to determine if there are two periods in a field. INSTR will find one, but how do I count specific characters?
I'm not sure how you'd want to apply it to your particular situation, but you might want to have a look at this [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001278;p=1#000000"]solution for counting characters[/url].
IF() statements can be dealt with using the "Nick Osdale-Popa" method seen [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001035#000004"]here[/url].
It may help you.
I think the fixed columnar structure of the report, despite the possible spread to 2 lines, is still a useful factor in this.
Kruncher's character count reference may well provide a method of counting the number of occurrences of " v " strings (as I recall strings are possible) on line one or decimal points on line 2 using the TEXTLINE function of version 8.
If you know how many field have dropped it would allow you to RSPLIT the multiline field and pick off the dropped fields reading back from the right or something similar depending on how the multiline functionality has handled spaces.
If you use INSTR to find the positions of the " v " strings that should tell you which field has dropped. For multiple dropped fields you just need to repeat the INSTR idea but set up the formula so that the search starts after the occurrence of the first " v " string - and so on.
If I can make enough time I will play around and see what can come up with.
Time was of the essence, and I lost track of the trick of stringing IF statements (thanks for supplying the link), so I knocked out a solution for each field that looks at the field to its left. Example for field H:
This looks for a V in the field. If found, it returns 0. If there's a decimal in both the target field and its neighbor, it returns the field value. If there's a V in the neighbor, it returns the digits in the neighbor with the field. Otherwise it returns the neighbor plus the field (for cases where the field overlaps to its neighbor, but the neighbor is empty). The "val" statement turns all the originally trapped character fields into numerics.