3 Replies Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Preceding String Solution

    LARRYG _

      Hi All,

       

      I started another post to summarize the solutions to my previous post.

       

      First of all, I would like to thank Grant, and Dee Moore for their help and feedback, and anyone else who contributed. Your feedback was very

      helpful and much appreciated.

       

      Example # 1:

      5 line Total Block where fields on lines 2 - 5 are variable. Meaning some blocks have 2 lines, 3 lines, etc. and there are enough lines between blocks as to not cause an overlap of fields from one block to another.

       

      Totals For Group 010 Paid:

                           Prior Mo:

                           Checks:

                           Voids:

                           Net:

       

      Totals For Group xxx Paid:

                           Prior Mo:

                           Checks:

                            

       

       

      Totals For Group 011 Paid:

                           Checks:

                           Voids:

                           Net:

       

      Solution # 1:

      Highlight 5 line sample and trap on T and G of first line. Highlight fields to right of labels and on lines 2 - 5 select Advanced Tab to define preceding string (ie: prior mo for line 2 field)

      Create Footer and select blank line after Net: as sample and enter a blank character trap above first character of Paid. There is no need to create any footer fields. This will select all blank rows as a footer following the detail template and stop the preceding string search.

       

      Case # 2:

      If you don't have enough lines between blocks as illustrated below, you need solution #2.

       

      Totals For Group 010 Paid:

                           Prior Mo:

                           Checks:

                           Voids:

                           Net:

       

      Totals For Group xxx Paid:

                           Prior Mo:

                           Checks:

       

      Totals For Group 011 Paid:

                           Prior Mo:

                           Checks:

                           Voids:

                           Net:

       

      Solution # 2:

      Select first row as sample

      Highlight the Paid label and amount as one multi-line field.

      Select the Advanced Tab and enter preceding string Paid.

      Select end field with Left Justification.

      Create 5 calculated fields and define formula to extract amounts. It involves Val, Instr, Substr functions. You can email Dee Moore@Datawatch for details. The second solution works but it's much more tedious. I believe there should be an option to tell Monarch when the detail template should end so the first solution would work in both cases.

       

      Regards,

       

      Larry

        • Preceding String Solution
          Grant Perkins

          Hi Larry,

           

          A good summary for all who later read this thread but can I add to it?

           

          The matter of reports with some very short records (minimum number of lines between the start lines of 2 records) but also some longer records can often be dealt with by a variation on one of the earlier solutions.

           

          For example, if you have records which at their smallest offer just 3 lines to work with but in some cases may have up to 15 lines you can set up templates based on a 3 line sample and have one as a detail and 4 (or more) as 3 line Append templates using the same trap as the detail template but 'selecting' the other possible fields using the preceding string method to pick the field. The effect should be the same.

           

          There are limits, in so far as there are limits to the number of templates which can be defined, but in most cases it is possible to produce a result. Huge differences in the number of lines between the smallest (limiting) template and the very largest might require an entirely different approach.

           

          For anyone reading this I whole heartedly recommend experimentation as a means of discovering new options. There are always new approaches to be discovered and developed.

           

          Grant

          • Preceding String Solution
            LARRYG _

            Grant,

             

            I tried the append approach as follows:

             

            Totals for Group

            Paid: (Detail template)

            Prior Mo: (Append 1)

            Checks: (Append 2)

            Voids: (Append 3)

            Net Checks: (Append 4)

             

            What happens is that the append always follows the current record, so If I should have any of the append type fields, they would be included as part of the next record, which I wouldn't want.

             

            Regards,

             

            Larry

            • Preceding String Solution
              Grant Perkins

              Hi Larry,

               

              Did you use the same trap for all templates. (Your T and G on the first line for example?)

               

              Basically this idea is the same as single template solution with 'floating' preceding string field identifiers but adapted across multiple templates where the template sample cannot provide enough lines in which to define all the fields required without a risk of overlapping some of the records and therefore not selecting them at all.

               

              If you always use the same trap line OR a trap that STARTS before the detail trap starts it should work, or so I have found. The preceding string must be unique within the record for success. If the are 2 occurrences only the first would be picked.

               

              Grant

               

              Originally posted by LARRYG:

              Grant,

               

              I tried the append approach as follows:

               

              Totals for Group

              Paid: (Detail template)

              Prior Mo: (Append 1)

              Checks: (Append 2)

              Voids: (Append 3)

              Net Checks: (Append 4)

               

              What happens is that the append always follows the current record, so If I should have any of the append type fields, they would be included as part of the next record, which I wouldn't want.

               

              Regards,

               

              Larry /b[/quote]