6 Replies Latest reply: May 15, 2014 9:54 AM by Acy _ RSS

    Having problems gathering multiple lines...

    Acy _

      The report is listed below, and while it doesn't seem to be spaced correctly, the first line in each section consists of column headings in the report, followed by actual data.

       

      There is ALOT more to the report than this, but this is my detail, and all I need at the top is the account number to go with this particular detail section.

       

      My problem is that the first line under Comments is actually a continuation of the first line under Passcode. And that's the way I would like to extract it. I am currently using Monarch v5, but will be switching to v7 sometime in the next 2 months. Not in time to solve this problem I'm afraid.

       

      Any way I can get these lines to end up on the same record.

      [font="courier"] Passcard   -


      Name -


      A Usr -


      Phone -


        Msg Msg Msg Msg Msg

                  ANYCITY PD/FD/EMS           Global              FIR FIR ?UR PAN    

                  KIM SMITH                   1 (999) 999-9999    ??? ???            

                  MONICA BROWN                1 (999) 999-9999    ??? ???            

                  METROPOLIS POLICE           Global              ?UR ?UR            

      AT19998887 CONFIRMATION NUMBER       

       

      /font[/quote][quote][font="courier"]                                  

      Comment        Days        Time           Date       Purge

      MOBILE        YYYYYYY   hh:mm  hh:mm   mm/dd  mm/dd     N

                    YYYYYYY   hh:mm  hh:mm   mm/dd  mm/dd     N

                    YYYYYYY   hh:mm  hh:mm   mm/dd  mm/dd     N

                    YYYYYYY   hh:mm  hh:mm   mm/dd  mm/dd     N

                    YYYYYYY   hh:mm  hh:mm   mm/dd  mm/dd     N /font[/quote]Here is how I would like for it to end up:

       

       

      [font="courier"]   Passcard   -


      Name -


      A Usr -


      Phone -


        Msg Msg Msg Msg Msg   Comment        Days        Time           Date       Purge

                    ANYCITY PD/FD/EMS           Global              FIR FIR ?UR PAN       MOBILE       YYYYYYY   hh:mm  hh:mm   mm/dd  mm/dd     N

        /font[/quote]Thanks in advance for any help!

       

      [size="1"][ November 07, 2003, 03:38 PM: Message edited by: Acy ][/size]

        • Having problems gathering multiple lines...
          rnd _

          Have you tried highlighting the top two lines.

           

          Passcard   -


          Name -


          A Usr -


          Phone -


            Msg Msg Msg Msg Msg            ANYCITY PD/FD/EMS           Global              FIR FIR ?UR PAN    

          Then use Passcard as the trap.  Extract data below it by defining the fields.  If you need additional fields, use append.

          • Having problems gathering multiple lines...
            Acy _

            I can successfully trap the lines I need, the problem is, that I have 2 sections of detail that I need to show up in the same table record.

             

            They way I tried doing it was to use two seperate models, but then once I got them into a database, I didn't have any way to link or correlate the two halves.

             

            Not sure about Version 7, but Version 5 doesn't have an auto-incrementing identifier column that I can use. Or at least I can't find it if it exists.

             

            One thing I tried was using a formula field with "line number" and that would have worked GREAT, exactly what I needed, if it wasn't for one simple flaw, at some point the line numbers were resetting and starting over at 1 again. And so I ended up with duplicate line numbers and that doesn't work for a unique identifier.

             

            I tried using a multi-line trap, and then stuffing all the data I needed into one big multi-line field so I could use string functions either in Monarch or in MS SQL to parse through the field and move everything into seperate fields, but the problem with that, is that the field length wouldn't have been constant, some of the Accounts in the report had as many as 30 contacts, and others as few as 1 or 2.

             

            Having it stop the field multi-line field when it encounters a blank line wouldn't have worked either, because there are blank lines between the two sections that I want to concatenate.

             

            Any further ideas would be greatly appreciated.

            • Having problems gathering multiple lines...
              Grant Perkins

              Acy,

               

              Seems like you have considered most of the regular things like the multi-line solution which can be great but gets too complicated with up to 30 'records' and may even start to hit field size constraints on those numbers. You could extend over the blank lines by using the last "end on" option "minimum action" and stopping at the next template.

               

              Did you try adding Page Number as well as Line Number? Then I assume your Account number can be a Page Header or Append to both sections of data?

               

              Actually Page header can be whatever you want ti to be so you can sort of re-construct how the data is grouped beyond the physical format.

               

              Taking that a stage further how about re-ordering the report? There is a great example of that provided as part of Datawatch User Conference 2003 CD. Ideally if you could match each part one line with its extension under the Comments section you would simply have a 2 line record. Or maybe then process that the <crlf>'s out and get a single line record.

               

              Not sure if you will be able to find  way to number each full line (entire line as a single feld at this point) and re-sort on a single pass. But if you can then simply export the resulting table to a text file and use that as the report to model and extract the data.

               

              If you can't get to a line re-sort on one pass, do it in two by selecting entire lines but adding relevant page(), line() an possible recno() in the data table. (Which ever works best, I don't feel able to be more specific without a full file to play with). Combine the 2 files and then sort.

               

              I assume that "Account number" (or whatever the report is grouped by)  is available via an append or page header to both sections of data. That would give at least a partial link.

               

              Also if you where to export via a summary you could add page and line number AND perhaps a COUNT set at MAX to indicate how many detail rows there were for that record group. It might be a useful number upon which to base other calculations.

               

              The User Conf example takes some summary footer rows and moves them to near the top of the report to make them available for use via an append.

               

              If you can get a link somehow, perhaps based on the Account Number and a Page Number & line number value used to generate a calculated field so that it would give the same reference number for each part, then you could combine them by loading one part and, if you have 5 Pro, linking the second part of the line to the first part via a database join. Then export that (or not if you feel like parsing the 2 large fields as part of the same model) and use the result as the basis for the final extraction.

               

              A few ideas which I hope mean something in the context. I am happy to try to clarify anything that is unclear or you feel would benefit from further consideration.

               

              Good luck.

               

              Grant

               

              Originally posted by Acy:

              I can successfully trap the lines I need, the problem is, that I have 2 sections of detail that I need to show up in the same table record.

               

              They way I tried doing it was to use two seperate models, but then once I got them into a database, I didn't have any way to link or correlate the two halves.

               

              Not sure about Version 7, but Version 5 doesn't have an auto-incrementing identifier column that I can use. Or at least I can't find it if it exists.

               

              One thing I tried was using a formula field with "line number" and that would have worked GREAT, exactly what I needed, if it wasn't for one simple flaw, at some point the line numbers were resetting and starting over at 1 again. And so I ended up with duplicate line numbers and that doesn't work for a unique identifier.

               

              I tried using a multi-line trap, and then stuffing all the data I needed into one big multi-line field so I could use string functions either in Monarch or in MS SQL to parse through the field and move everything into seperate fields, but the problem with that, is that the field length wouldn't have been constant, some of the Accounts in the report had as many as 30 contacts, and others as few as 1 or 2.

               

              Having it stop the field multi-line field when it encounters a blank line wouldn't have worked either, because there are blank lines between the two sections that I want to concatenate.

               

              Any further ideas would be greatly appreciated. /b[/quote]

              • Having problems gathering multiple lines...
                Acy _

                Thanks Grant! I'll have to wait till monday to look and see if page number is an option in v5 I didn't see it. But then again I got v7 upgrade aproved, so I should be working on it in the next couple weeks.

                 

                Am I understanding this right? If I set account number as the header template, that will change the page breaks to break before each account listing? Even if the account listing is 3-4 physical pages?

                 

                Also, you mentioned Recno() as a calculated field value? I didn't see it in there, but then again I wasn't looking for it either. What does it pull it's value from? Table record? Because that could be the unique ID I'm looking for.

                • Having problems gathering multiple lines...
                  Grant Perkins

                  Acy,

                   

                  If you have V5 Help installed you can get a description of the functions available by using the Index tab in the Help window and entering Page Function, Line Function, etc,.  Rowno and Recno are worth reading as well. Recno is the record number from the full table according to what the template is selecting (or the row from a database if you have Pro). Rowno() offers the same concept but from the FILTERED and SORTED data set. If no filter or sort has been applied it will be the same as RECNO()

                   

                  Have a look for SETTINGS and SPECIFICATION as well - some stuff in there that might be worth knowing. You can set how many rows make up a page, with some intelligent red ahead features for blank lines, up to a maximum of 256 rows. Check out the Input options first before going and hacking values in the registry!!

                   

                  I don't think you will need to make changes at this level, although I usually run with page rows set to the 256 maximum so I may not be seeing issues taht other are.

                   

                  You should be able to establish a logical record break for the report and and make this a Page Header template and it does not have to be a 'physical' page header BUT be aware that the Page Header absolutely stands alone. You cannot map, say, another append template into the header space and it does require at least one line. This is not normally a problem but may require careful handling - it depends on the report.

                   

                  Also be careful if your report file carries repeat headers as it crosses pages. Normally they would not cause any problems, unless the new page introduced a change to key values somehow, but where you are looking to join 2 row of data that might well be on separate pages - real or virtual by way of the virtual page break, some pages my not offer quite as clean an extraction as others.

                   

                  If we consider a 2 stage process (in case the single stage is as tricky as it seems it might be) if you can set your Page Header and then an extract for the first block of lines and add RECNO() then repeat that with a model that just extracts the "second half of the lines" records you should get the same RECNO for reach of the 2 parts as long as every line always has both parts of the line recorded. Even if, for some reason, you get a different record number it should be a known offset from the 'real' number and so can be recalculated to match using a calculated field and adjusting for the offset.

                   

                  See if any of those give you a route forward. If you have the Pro version the join is easy of course, once you have an appropriate join key. If not it would be plan be and work out a way to sort the lines into consecutive lines for each logical record in a 'new version' of the report and then work with that for the final pass to define the required fields.

                   

                  Good luck. If anything else arises I hope you will let us know.

                   

                  Grant

                   

                   

                  Originally posted by Acy:

                  Thanks Grant! I'll have to wait till monday to look and see if page number is an option in v5 I didn't see it. But then again I got v7 upgrade aproved, so I should be working on it in the next couple weeks.

                   

                  Am I understanding this right? If I set account number as the header template, that will change the page breaks to break before each account listing? Even if the account listing is 3-4 physical pages?

                   

                  Also, you mentioned Recno() as a calculated field value? I didn't see it in there, but then again I wasn't looking for it either. What does it pull it's value from? Table record? Because that could be the unique ID I'm looking for. /b[/quote]

                  • Having problems gathering multiple lines...
                    Acy _

                    Thanks Grant, Recno() is exactly what I needed. I only had one problem with the top section, I couldn't get the detail without getting the column headers along with it, which skewed the Recno() so they didn't quite match up, but I was able to fix that in MS SQL with a cursor and a delete statement.

                     

                    I've checked about 100 records now and they all match up great.

                     

                    I should have looked in help, the thought never occured to me I guess. I probably would have found recno() if I had looked. Anyway I did read the manual, but the only manual I could find was for Version 4, so it wasn't very helpful. I'll be glad when Version 7 gets here, I'll have new current manuals and lots of cool features!