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

    Trapping Data with Multiple Lines

    dabronx _

      I have been able to trap the fields in the first line but I am having a little trouble trapping the "AS OF" date in the report below.  As you can see the "AS OF" date is not in every record and each record has lines that vary from 2-5 lines.  Specifically, in the sixth field, I need the first line from all records and the "AS OF" date if present.

       

      [font="courier"]    Payment Period:01-30-2006 to 01-30-2006                                                                               

      01-27-2006         TT0047D        TALIDA CAPITAL                         B                 50,000.0000    TRACKS GOLD TRUST                                                                               

      AS OF JAN 24,2006        

            01-27-2006         TT0062D        STREET GLO                             S                  5,000.0000    TRACKS GOLD TRUST                                                                               

      AS OF JAN 24,2006        

            02-02-2006         TT0112C        UTCA - V75B                            S                     15.0000    IMPERIAL                                                                               

      TRADED ON THE TSX        

            02-01-2006         TT0190C        FRONT                                  B                  3,250.0000    TELINC                                                                               

      AVERAGE PRICE                                                                               

      AVG PRICE-DETAILS ON REQ                                                                               

      TRADED ON THE TSX                                                                               

      AS OF JAN 27,2006        

            02-01-2006         TT0191C        FRONT                                  B                 12,750.0000    TELINC CORP                                                                               

      AVERAGE PRICE                                                                               

      AVG PRICE-DETAILS ON REQ                                                                               

      TRADED ON THE TSX                                                                               

      AS OF JAN 27,2006        

            02-01-2006         TT0192C        FRONT                                  B                    750.0000    TELINC CORP                                                                               

      AVERAGE PRICE                                                                               

      AVG PRICE-DETAILS ON REQ                                                                               

      TRADED ON THE TSX                                                                               

      AS OF JAN 27,2006        

            02-01-2006         TT0193C        FRONT                                  B                  3,500.0000    TELINC CORP                                                                               

      AVERAGE PRICE                                                                               

      AVG PRICE-DETAILS ON REQ                                                                               

      TRADED ON THE TSX                                                                               

      AS OF JAN 27,2006        

            02-01-2006         TT0494C        FRONT                                  B                  4,750.0000    TELINC CORP                                                                               

      AVERAGE PRICE                                                                               

      AVG PRICE-DETAILS ON REQ                                                                               

      TRADED ON THE TSX                                                                               

      AS OF JAN 27,2006        

               

      /font[/quote]

        • Trapping Data with Multiple Lines
          Grant Perkins

          Will you have at least 2 line printed for every record?

           

          If so use a 2 line data sample - easiest if you make one of the records with AS OF on the second line.

           

          Trap as you already do. Map the first line fields.

           

          Paint a field on the second line where the date will be. Oversize the field slightly if it is of variable length.

           

          Go into the field properties - advanced properties. In the 'Start Field On' section enter AS OF in the preceding string option.

           

          You may need to play with the field definition if you want it to be a date rather than character format but it might be recognised as is. If not take is as character and convert using a calculated field.

           

          The date will be picked up only where the preceding string exists on a line between the first lines of two detail records. Whether that is the second or a later line will not matter.

           

          HTH.

           

          Grant

          • Trapping Data with Multiple Lines
            dabronx _

            Thanks,  That works perfectly!!

            • Trapping Data with Multiple Lines
              dabronx _

              After implementing your suggestions it seems the model works for 99.9% of the data I am capturing. At the time I posted, there were at least two lines of data.  Since then a report has come up with a record or two with only one line of data. Any thoughts on how to capture those records?

               

              [size="1"][ February 22, 2006, 04:28 PM: Message edited by: dabronx ][/size]

              • Trapping Data with Multiple Lines
                Grant Perkins

                Such things always seem to happen just when you think the coast is clear ...      

                 

                There are a couple of options probably but as you will have guessed I'm sure, it gets messier.

                 

                Option one.

                 

                Reduce your template sample to a single line. Where the description part of the line would normally have the second line field beneath it, make the first line field a multi-line field but without a preceding string for the start.

                 

                That means that you will now extract ALL lines into a single field.

                 

                Now you need to split that field into the first line description and 'the rest. If you have Version 8 you can use the TEXTLINE function for that. If you don't have V8 we will need a different approach.

                 

                For now I will assume you have V8 and can split the first line from 'the rest'.

                 

                Using 'the rest' you need to check to see if the "AS OF" string exists. If it does not you can ignore that record. If it does you want to know where it is in the string. Once you have an indcation of where it is it should be possible to work out where the date you need will be.

                 

                The INSTR function should allow you to find the start of the "AS  OF" string when it exists. The SUBSTR function should then allow you to define where the date starts relative to the "AS OF" string and provide the extraction for you into a new calculated field.

                 

                I could work and example model for you but I think you will be able, as withthe last suggestion, work this one through for yourself. If not let me know and I will provide an example.

                 

                Option 2

                 

                The other option that comes to mind is the method you would almost be using if the "AS OF" preceding string concept was not available, though in this case we can still make use of it to eliminate the need for even more complex slicing and dicing.

                 

                In effect you would need a 2 phase process where phase 1 looked for the records with 2 or more lines and extracted lines 2 to xxx into an export file with a 2 column structure. Column one would be a key to connect back to the report record and column 2 the text to be linked into the second phase. I think it should be possible to work the extract so that the lookup table only had records to look up when the "AS OF" date existed.

                 

                The second phase of the process would then re-read the report, first lines only, and then link in the date from the external lookup. (I am assuming you have a Pro version of Monarch with external lookup capability. If not everything gets even more interesting ... but not impossible.)

                 

                I hope this helps.

                 

                Grant