6 Replies Latest reply: May 15, 2014 10:11 AM by Grant Perkins RSS

    Monarch v11 - Field widths change depending on content - stuck!

    jszeglin _

      Hello everyone,

       

      I'm in the middle of wrangling a relatively heinous report using Monarch v11.  In most cases, the detail records are divided into three fixed width sections on the same line - field name, old value, and new value.  However, when the first field contains the value "MEMO", the second and third fields then expand to contain the longer character string, and the third field is bumped to the next line.  An example is below:

       

         121212-00  RU *BOB     30998 0725 0023 0006 MEMO                            1004                        20110725

                       ACCOUNT              0                                             121212                        

                       NUMBER               0                                             1004                          

                       ENTRY-DATE           0                                             20110725                      

                       MEMO                 THIS IS THE OLD MEMO THAT GOES ACROSS MORE THAN ONE FIELD FUN TIMES    

                                            THIS IS THE NEW MEMO THAT GOES ACROSS MORE THAN ONE FIELD OH JOY   

                       EXPIRATION-DATE      0                                             20110726                       /CODE

       

      The first line is an append record and I can parse that without any problems.

       

      The next six lines are detail records.  I can easily get the "Account", "Number", "Entry-Date" and "Expiration-Date" data ("field name", "old value", and "new value"), but I cannot get the complete "Memo" data because the widths for the "old value" and "new value" fields roughly double and the "new value" field is pushed to the next line.

       

      I've messed around with the advanced field properties and there doesn't appear to be a way to grab what I need.  Any help would be greatly appreciated!

       

      Regards,

       

      John

        • Monarch v11 - Field widths change depending on content - stuck!
          Olly Bond

          Hello John and welcome,

           

          From the sample, defining the Field Memo to end on "end of left justification" would seem to work. Does every record have the same field structure? If you can email be a bigger sample I'd be happy look into into it in more depth.

           

          Best wishes,

           

          Olly

            • Monarch v11 - Field widths change depending on content - stuck!
              jszeglin _

              Hello Olly,

               

              Thanks for the reply!  Let me provide a bit more background and context.  This is an audit report that provides information when specific fields within a database are changed.  The fields listed can vary depend on what was changed - there is not a set list.

               

              Because of this, I'm not defining "Memo" as its own field within Monarch.  Rather, the field is called "Field Name" and can contain a bunch of different values, in this case "Account", "Number", "Entry-Date", "Memo", and "Expiration-Date".  The next two fields reflect the old and new values respectively.  These fields can be alphanumeric, numeric, etc - no set structure.  I am defining them as character fields.

               

              If there is a way to treat "Memo" as its own unique field, I can try that as well. 

               

              Here is a larger set of data:

               

                                 TLR  SEQUENCE TRAN       ACC                                                                ENTRY

                    ACCOUNT  SN   ID   NUMBER  DATE   BR  CTR CODE                          NUMBER                            DATE

               

                                FIELD CHANGED        BEFORE                                        AFTER

               

                    9999-02  XX  123        10 0728 0012 0026 WMRX                             200                        20110728

                                MEMO                 000019250/000000/000000/000000/019250/S  WMRX:             *

                                                     000033000/000000/000000/000000/033000/S  WMRX: 000013750   *

               

                  654321-00  YY  234     26744 0728 0017 0017 WMRX                             200                        20110728

                                ACCOUNT              0                                             654321

                                NUMBER               0                                             200

                                ENTRY-DATE           0                                             20110728

                                MEMO                 *BLANKS*

                                                     000010000/000000/000000/000000/010000/S  WMRX:

                                EXPIRATION-DATE      0                                             20110804

               

                  765432-00  ZZ  345     26744 0728 0017 0017 WMRX                             200                        20110728

                                MEMO                 000010000/000000/000000/000000/010000/S  WMRX:

                                                     000012500/000000/000000/000000/012500/S  WMRX: 000002500

               

                  876543-02  AA  456     25282 0728 0021 0063 ATSD                             200                        20110728

                                ACCOUNT              0                                             876543

                                SUFFIX               0                                             2

                                NUMBER               0                                             200

                                ENTRY-DATE           0                                             20110728

                                MEMO                 *BLANKS*

                                                     000270000/000000/000000/000000/025000 ATSD:                *

                                EXPIRATION-DATE      0                                             20110730

               

                  987654-02  HH  567     25363 0728 0021 0063 ATSD                             200                        20110728

                                MEMO                 000270000/000000/000000/000000/025000 ATSD:                *

                                                     000295609/000000/000000/000000/025000 ATSD: 000025609      *

               

                  214321-00  CC  654     30694 0728 0016 0054 MEMO                           30000                        20110728

                                ACCOUNT              0                                             214321

                                NUMBER               0                                             30000

                                ENTRY-DATE           0                                             20110728

                                MEMO                 *BLANKS*

                                                     BB345 7/28/11 RCVD RTN 615 0.00  FROZEN/BLOCK ACCT.

                                EXPIRATION-DATE      0                                             20210728[/CODE]

               

               

              Thanks again for the assistance.

               

              -John

                • Monarch v11 - Field widths change depending on content - stuck!
                  Olly Bond

                  Hello John,

                   

                  Assuming that every account number has at least one field that has been changed, which I think is safe as an audit report would naturally only report changes, then I'd define the model using a single line detail trapping on BB0 (blank, blank, non-blank) picking up the data in BEFORE. Define fields for BEfORE and for FIELD, for the latter tick the box "Copy values from previous record" to handle blanks like the second line of MEMO.

                   

                  Then define an append to pick up the relevant account number and dates, and you should be able to get whatever you need using filters and possibly a summary.

                   

                  Hope this helps,

                   

                  Olly

                    • Monarch v11 - Field widths change depending on content - stuck!
                      jszeglin _

                      Ollie,

                       

                      Thanks again for the reply.  What you're saying makes sense and I am basically doing that now, but here is what I am getting tripped up on:

                       

                      When the "Field Name" value is "MEMO", the "Before" and "After" fields are on two separate lines, and are 75 columns in width each - column 40 to column 115.

                       

                      When the "Field Name" value is not "MEMO" (e.g. "ACCOUNT", "NUMBER", etc), the "Before" and "After" fields are on the same line, and are 30 columns in width each.  "Before" is between columns 40 through 69, and "After" is between columns 86 through 115.

                       

                      I don't know how to set up a detail record that can cover these two different widths.  It seems like I need to run through the report twice - once to pick up fields where the "Field Name" is not "MEMO", and once again to pick up the fields where the "Field Name" value is "MEMO".

                       

                      But to define two different detail records to accommodate the two different widths, I would need to create two different models for the same report file and then filter records out by the "Field Name" value equal or not equal to "MEMO", and then append the output of the two models to the same extract file.  Sounds a bit kludgy but perhaps that's the only way?

                       

                      I appreciate your time!

                       

                      -John

                        • Monarch v11 - Field widths change depending on content - stuck!
                          Olly Bond

                          Hello John,

                           

                          You can do all this work in the table - once you've trapped what you need in the Report window. To get what you need, we'll have to take a slightly different approach and trap the FIELD and VALUE lines so that they cope with the MEMO records as one line of detail and not as two. Define the trap on the FIELD data, and define the field properties for VALUE so that it Blank field values of 1.

                           

                          Assuming that you've got the two fields and , where value starts in column 40 and has a data length of 80, and in some instances has one line but in MEMO cases as two. Now we can define two calculated fields called BEFORE and AFTER as follows:

                           

                          BEFORE: if(="MEMO";Textline(;1);substr(;1;30))

                           

                          AFTER: if(="MEMO";Textline(;2);substr(;45;30))

                           

                          In essence, we're telling Monarch to, if the record is a MEMO record, use the first and second lines of the VALUE field, but if not, to use substrings startng at specific positions and with defined lengths.

                           

                          Hope this helps,

                           

                          Olly

                            • Monarch v11 - Field widths change depending on content - stuck!
                              Grant Perkins

                              John,

                               

                              There is another possible approach although it's usefulness here may depend on the number of possibilities for the fields that can be changed and the requirements for your output.

                               

                              Briefly ...

                               

                              If you make the Account detail line your DETAIL record you can then define some append records using the same template trap as the detail line but setting the sample rows to 3 (that seems to be the probably minimum number of report printed/empty  rows between Account lines) and then defining the fields you want in their correct positions and sizes across the rows but ignoring their vertical positions. For each field to be extracted use the 'advanced features' of the field properties to set a 'preceding string' that uses the database field name. Use as many append templates as you need to get all of the fields that are likely to change. In this case with 3 sample lines to play with and up to 19 possible append templates you can work with up to 57 'change' fields from the report.

                               

                              Using the original field name as a preceding string means that Monarch will search for that string and only populated the fields when it appears (a sort of filter!). Where it appears in the list does not matter so long as the preceding string is unique.

                               

                              For each 'Account' record line that appears in the report you would end up with a single output record listing all changed fields plus any other fields that are reported as changes for another account somewhere in the report. These will, of course, be empty for records where thay have not been changed.

                               

                              This is actually more difficult to try to explain than it is to show and do. If you search through the forum for the so-called 'guru trap' you should find some examples that look similar to your requirements.

                               

                              It is entirely possible that the form of output it produces is not what you require in which case Olly's solution looks like the way to go.

                               

                              HTH.

                               

                               

                              Grant Perkins