14 Replies Latest reply: May 15, 2014 9:55 AM by KEVIN KENNEDY RSS

    Multiple-line field that isn't


      I am modelling a report that puts the letter "V" if the data is too large to fit in its normal field on a line. When this happens, the data is printed in the same horizontal position one line below the "V."  Using multiple-line field doesn't seem to work, and variable-line field seems to work only horizontally, not vertically. Is there any solution other than to create two models, one for the non-"V" lines and one for the "V"-lines?

        • Multiple-line field that isn't
          Grant Perkins

          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.






          • Multiple-line field that isn't
            KEVIN KENNEDY

            The way the "V" data prints out is:

            otherdata otherdata     V   otherdata otherdata


            versus normally:

            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.

            • Multiple-line field that isn't
              Grant Perkins

              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;


              otherdata otherdata V otherdata otherdata


              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!


              Best regards,




              • Multiple-line field that isn't
                KEVIN KENNEDY

                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.

                [font="courier"]CLOSING LINE 111022.84 175395.03   1192.85      V   382975.54             439833.42     


                CLOSING LINE  48120.94  32268.33      V   1091017.94  9766.36-              2458.00       

                ...................................7766.00 /font[/quote]

                • Multiple-line field that isn't
                  Grant Perkins

                  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.




                  • Multiple-line field that isn't
                    Grant Perkins

                    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.


                    So ....


                    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.


                    Set the field to be multi-row ending on a blank field position if there is always a blank line between records. Otherwise something else suitable. Field is called in the examples below.


                    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.


                    Column 2.


                    [font="courier"]val(lsplit(intrim(),3," ",2))[/font][/quote]Assumed that no "V" is likely so the same formula but shifted the split section selected to get the secomnd column.


                    Column 3


                    [font="courier"]if((lsplit(intrim(),4," ",3)="V")

                    ,val(rsplit(intrim(),3," ",2))

                    ,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.


                    Column 4


                    [font="courier"]if((lsplit(intrim(),5," ",4)="V")

                    ,val(rsplit(intrim(),3," ",2))

                    ,val(lsplit(intrim(),5," ",4)))[/font][/quote]Much the same, just change the section selected.


                    Column 5


                    [font="courier"]if((lsplit(intrim(),6," ",5)="V")

                    ,val(rsplit(intrim(),3," ",2))

                    ,val(lsplit(intrim(),6," ",5)))[/font][/quote]Same for col 5.





                    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!






                    • Multiple-line field that isn't
                      KEVIN KENNEDY

                      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.

                      • Multiple-line field that isn't
                        Grant Perkins

                        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".

                        • Multiple-line field that isn't
                          KEVIN KENNEDY

                          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.

                          • Multiple-line field that isn't
                            Grant Perkins

                            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.






                            • Multiple-line field that isn't
                              KEVIN KENNEDY

                              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?

                              • Multiple-line field that isn't
                                Data Kruncher

                                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].




                                • Multiple-line field that isn't
                                  Grant Perkins

                                  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.





                                  • Multiple-line field that isn't
                                    KEVIN KENNEDY

                                    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.