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

    Multiline trap to Multi Record

    Ivan _

      Hello Monarch gurus,

       

      I’ve been using Monarch 5.02 for a year now, I’ve come across a problem I can’t seem to figure out, I have a report on which the detail can be of ‘n’ number of lines, because most of the times the report doesn’t keep it’s alignment is not possible to use a simple trap but the multi-line works fine, the problem is that on the table view there’s only one field with all of the ‘n’ lines concatenated, is it possible to make each line to be a record by it self?

      Here’s a fragment of the report:

       

      [font="courier"]Deliver Parts To: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX            -


      Requested Rate and Flow -


                  

                  CHIH3    XXXXX XXX XXXX                                Quantity    Date          Quantity  Date             

                           XXXXXXX                    XX XXXXX                1  2005/07/01           1  2005/07/29                                                                               

      2  2005/08/12 /font[/quote]This is what on the table view:

       

      CODE               DATES

      CHIH3               1  2005/07/01           1  2005/07/29          2  2005/08/12    

       

      But I need something like:

       

      CODE               DATES

      CHIH3               1  2005/07/01           1  2005/07/29          

      CHIH3               2  2005/08/12    

       

      Thanx

        • Multiline trap to Multi Record
          Grant Perkins

          Ivan,

           

          When you say the report doesn't keep its alignment, what exactly is the problem?

           

          I am wondering if there might be a way to work around the problems of alignment for the trap.

           

          The example of what you require suggests that each line needs to be a detail line on its own and the CHIH3  field needs to be part of an append.

           

          If the detail is captured as a single field you could look at using the SPLIT functions (LSPLIT and RSPLIT) to separate it to what you require. But you will still have to obtain the CHIH3 from somewhere.

           

          Or you could consider 're-modelling' the entire report to make the presentation of the parts you require more consistent. That in turn would allow you to take control of the detail and append sections and deliver what you need that way.

           

          It would be a 2 stage process - re-model the report as stage one. Stage 2 - use the new report and a suitable model to extract the data fields you need.

           

          The detail of the problems from a slightly  larger sample would define the most likely best way to approach this I would be happy to take a look off forum if the information is of a confidential nature. If you send me a Private Message with your email address I will let you know where to send the file - assuming you are able and allowed to provide one.

           

          Grant

          • Multiline trap to Multi Record
            Ivan _

            Hi Grant,

            Yup that’s right each line has to be a detail but because of the alignment problem and the lack of trappable characters makes it rather difficult to trap just the information I need without getting garbage, I’ve been able to successfully mine the report that way (but it had require some processing and filtering in Monarch) and everything was ok until they told me that even if the detail line was empty I had to capture it, that’s what drove me to try other paths.

             

            This is the example of the alignment problem:

             

            [font="courier"]Deliver Parts To: XXXXXXXXXXX                                -


            Requested Rate and Flow -


                        CHIH1    XXXXXXXXXXXXXX                            Quantity   Date          Quantity  Date

                                 XXXX                  XX XXXX              2   2004/04/08             5   2004/04/15

                                                           5   2004/04/20           10  2004/04/25

            Special Instructions:

             

            Deliver Parts To: XXXXXXXXXXX                                -


            Requested Rate and Flow -


                        CHIH3    XXXXXXXXXXXXXX                            Quantity   Date          Quantity  Date

                                 XXXX                  XX XXXX              2   2004/05/08             5   2004/05/15

                                                        5   2004/05/20             10  2004/05/25

            Special Instructions:

             

            Deliver Parts To: XXXXXXXXXXX                                -


            Requested Rate and Flow -


                        CHIH1    XXXXXXXXXXXXXX                            Quantity   Date          Quantity  Date

                                 XXXX                  XX XXXX             

            Special Instructions:[/font][/quote]So my chances of using the “/” as a trappable character vanished, and even if the report kept aligned the new issue is capture that line even if it’s empty.

            • Multiline trap to Multi Record
              Grant Perkins

              Hmm, an interesting little challenge - but conquered (I think ...).

               

              Probably easiest if I send you my sample file extracted from the forum post and the model. Let me have your email address and I will send them on.

               

              To outline the approach I have treated each quantity/date line as the detail (trapped on having a number of spaces in front of the data required and always a blank character at the start of the line. In fact it may be more sensible to just select any line with a blank first character since it looks like all lines will have a number of blanks in the middle of the row and therefore they do not help when trying not to select unwanted lines. However there may be other report lines, not in the sample, where it would help so I used them anyway).

               

              The CHIH3 etc. fields trap as an append (trapped on the existence of a string in that field's position preceded by a blank).

               

              This will capture some unwanted lines but they can be filtered out. I decided to capture the field(s) you have marked with XX XXXX so that we can filter blank lines BUT retain the line if it the first line of a record - i.e. the blank detail lines you need to capture to have a record of the "deliver to" info.

               

              Finally I used a calculated field to reformat the 'untidily aligned' data for the quantities and dates. If you want to keep the data in one field (as I have done in the model) or separate it into different fields there should be no real problems PROVIDED the extend of misalignment is never much greater than you have shown in the sample. Indeed separate fields would be easier in that situation.

               

              It's much easier to see it in the model than to document it but I can try the documentation route if mailing the files to you is not possible.

               

              Grant