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

    v9 - Nasty append levels

    sambadancer _

      Dear all,

      Could anyone please help me figure out the following (anonymized) source data:

       

      [SIZE="1"]Invoice number        For information                   Concerns                     Sent              Page 

      000000011-20090500    0800-0408                         MAY 2009                     16 april 2009     2 of 5

                                                                                      Amount in €      VAT %

      ASD-GV/NP30

      A-SIDE: AAA Street 4

      B-SIDE: BBB Street 2

      STANDARD 64 KBIT/S

        MONTHLY FEE                                                                               

      CONNECTION A-SIDE                                                                        113.00        0.0

        31/03/09 TIL 30/04/09

        CONNECTION B-SIDE                                                                        113.00        0.0

        25/02/09 TIL 31/03/09

        ONE-TIME FEE

        FIXED FEE FOR DISTANCE                                                                    98.50        0.0

        AMOUNT OVER 46 KM                                                                        119.60       19.0

                                                                                      HT UT NP10

      A-SIDE: CCC Street

      B-SIDE: DDD Square 12

      EVPN 4 MB /S

        MONTHLY FEE

        CONNECTION  A-SIDE                                                                       113.00        0.0

        ONE-TIME FEE

        BUY-OUT                                                                                8,110.00       19.0                                                                               

      HTUTNP11

      A SID: EEE LANE 1

      BSIDE FFF ROAD 454

      DIGISTREAM 128 KBIT

        ONE-TIME FEE

        STANDARD ACCESS                                                                        1,075.00       19.0

        MONTHLY FEE

        FOR A DARK FIBRE DUAL                                                                    118.79        0.0

        25/02/09 TIL 31/03/09[/SIZE][/CODE]

       

       

      The resulting output should look like this (disregarding the header, which should be easy to extract):

       

      [SIZE="1"][B]LINE         A-SIDE        B-SIDE         SERVICE             COST_TYPE  REKORD                   AMOUNT  VAT  PERIOD[/B]

      ASD-GV/NP30  AAA Street 4  BBB Street 2   STANDARD 64 KBIT/S  MONTHLY    CONNECTION A-SIDE        113.00  0.0  31/03/09 TIL 30/04/09

      ASD-GV/NP30  AAA Street 4  BBB Street 2   STANDARD 64 KBIT/S  MONTHLY    CONNECTION B-SIDE        113.00  0.0  25/02/09 TIL 31/03/09

      ASD-GV/NP30  AAA Street 4  BBB Street 2   STANDARD 64 KBIT/S  ONE-TIME   FIXED FEE FOR DISTANCE    98.50  0.0

      ASD-GV/NP30  AAA Street 4  BBB Street 2   STANDARD 64 KBIT/S  ONE-TIME   AMOUNT OVER 46 KM        119.60 19.0

      HT UT NP10   CCC Street 1  DDD Square 12  EVPN 4 MB /S        MONTHLY    CONNECTION A-SIDE        113.00  0.0

      HT UT NP10   CCC Street 1  DDD Square 12  EVPN 4 MB /S        ONE-TIME   BUY-OUT                8,110.00 19.0

      HTUTNP11     EEE LANE 1    FFF ROAD 454   DIGISTREAM 128 KBIT ONE-TIME   STANDARD ACCESS        1,075.00 19.0

      HTUTNP11     EEE LANE 1    FFF ROAD 454   DIGISTREAM 128 KBIT MONTHLY    FOR A DARK FIBRE DUAL    118.79  0.0  25/02/09 TIL 31/03/09[/SIZE][/CODE]

       

      Well of course the Detail lines are very ease to extract, trapping on the two commas.

       

      But [I]every[/I][/U] other field... I've been trying for hours now, but to no great avail.

       

      Probably the "MONTHLY FEE" AND "ONE-TIME FEE" should be picked up using an Append, but they keep repeating themselves (I don't have v10 with the nice 'Cleared by...' feature). And what about the dates (field "PERIOD")?

       

      My hairs are turning slightly grey already... can someone help?

       

      Best regards and thanks in advance!!

       

      Bart...

        • v9 - Nasty append levels
          Olly Bond

          Hello Bart

           

          This sounds like a very good excuse to upgrade to v10 But I think you can trap the "FEE" lines safely as an append using the floating trap on ABFEEBB (that's an alpha trap, then a blank, then FEE as literal, then two more blanks) as this should pick up the examples in your sample. Line, ASide, BSide and Service should be another append, using a multi-line sample with four lines, trapping on AA (two alpha traps) in columns 1 and 2, then a lot of B blank traps to exclude "bad" lines.

           

          The Period you'll need to use Advanced Field Properties for, but it can be done. Define the source of the Rekord field as a MEMO type, and set it to end on "none of the above". Then you can use Textline(Rekord;1) as the OutputRekord, and Textline(Rekord;2) as the Period.

           

          Hope this helps,

           

          Olly

            • v9 - Nasty append levels
              Grant Perkins

              Hi Bart,

               

              I can confirm that Olly's suggestion works for the sample. I tested it with very minor differences.

               

              The detail template is trapped as you already know. Make the Rekord field a multi-line field using the advanced properties. I used 'End of left justification' to close the field when it does not encounter another template for a while - it saves it running into the blank lines but that is a cosmetic difference as far as I can tell from the sample. (Try both to see what I mean!) Olly's suggestion of using a MEMO field is a good one although in this case I don't think you need to and making it a regular Character field means you can set the width as you define it whereas, iirc, a Memo field automatically gets set to a rather wide value that you might then want to reduce.

               

              When creating the separate fields from the multiline field using TEXTLINE(), once you have generated the first field you can save some time by duplicating that field, name the new field as you need it and then simply edit the formula to grab line 2 instead of line 1.

               

              The "FEE" append as a floating trap I did slightly differently.  Firstly be sure to select as the SAMPLE DATA line the line with the longest Fee type name - in the sample posted that would be "One-Time". This is important for floating traps which, unlike other traps, make use of the selected data line matched with the traps in order to understand how to 'float' on the line. In effect the trap points not only tell Monarch how to find the line they also define where the fields are and how to work out how wide they can be.

               

               

              The trap, therefore, needs to be BfeeBB  (add more trailing Bs (blanks) if needed). If you include an ALPHA trap at the beginning you will lose the last character of the field you will need to define BEFORE the trap. (Again, try it - you will see what I mean).

               

              Finally the 4 header lines for Line, A-side, B-side and service. It looks like a simple trap on alphas in the first 2 columns and using a 4 line sample will be enough. (I am assuming that the 5 lines with the Invoice number above that will be another append or, better, a Page Header append.) For the A-side and B-side fields I defined them with an advanced property of a preceding string (a-side and b-side respectively) for a little more certainty of accuracy. However I cannot be sure that to do so will work for the entire report - indeed your sample has different strings for the 3rd record block but I assume this was an result of the data scrubbing for sharing in public.

               

              So, not a pleasant report but possible to do something with it. Or at least I think it is until you discover another part of the report where the data do not fit the same rules .....  

               

              Hope that helps.

               

               

              Grant

                • v9 - Nasty append levels
                  Olly Bond

                  Hello Grant, hello Bart,

                   

                  For the ABFEEBB trap, I was trying to exclude the possible appearance of a line with "FEE" appearing as a false positive match for the template. Yes, it meant that I had to define two fields, one of the "MONTHL" and another of the "Y FEE" and glue them together in the table, but I thought that was safer. The preceding string suggestion for the A & B sides is an excellent one - from the field list and sample it seems that the data is expected to have one A and one B record for each detail, but it's always dangerous to extrapolate. A longer sample would let us see how page breaks needed to be handled also.

                   

                  Best wishes,

                   

                  Olly

                    • v9 - Nasty append levels
                      Grant Perkins

                      Hello Grant, hello Bart,

                       

                      For the ABFEEBB trap, I was trying to exclude the possible appearance of a line with "FEE" appearing as a false positive match for the template. Yes, it meant that I had to define two fields, one of the "MONTHL" and another of the "Y FEE" and glue them together in the table, but I thought that was safer. The preceding string suggestion for the A & B sides is an excellent one - from the field list and sample it seems that the data is expected to have one A and one B record for each detail, but it's always dangerous to extrapolate. A longer sample would let us see how page breaks needed to be handled also.

                       

                      Best wishes,

                       

                      Olly[/QUOTE]

                       

                      Good point on the 'fee' trap Olly, as ever.

                       

                      The interesting thing about so many of samples is that one can never be entirely sure how consistent they are across a full report. How many times do we see things that work fine for thousands of lines but then fail on one or two somewhere!

                       

                      These 'plan B' techniques are rather important as concepts to be applied wherever they may be required. It may be worth collecting them into a reference document at some point. Of course whether an approach is Plan A or PLan B might be open to discussion .....! 

                       

                       

                      Grant