9 Replies Latest reply: May 15, 2014 9:59 AM by Gene _ RSS

    Multi Line Trap Confusion

    Gene _

      I'm having issues.

       

      I've got a report where sometimes the record is one line long, other times it may be 6 or 7 lines long.

      I've gone thru multiple other posts on the forum regarding multiple line traps, however, I'm not getting the data to look correct after trapping.

       

      My report resembles this:

       

      [font="courier"]5000009161204443790061007326                                    

      5500    696994354003100097500200994354310002               0   

      5300000019500000000000000000                                   

      5970000000000000000000000000000000000000000000000000           

      600000001950                                                   

      609900000000                                                   

      4100009163408007326                                             

      4000009163412000023                                             

      540000900023163436    696994313150                              

      540000900023163522    696994356150                              

      4100009163525000023                                             

      4000009163555007326                                             

      5000009164309443820061007326                                    

      5500    693194328000500000000200994328050002               0   

      5300000000000000000000000000                                   

      5970000000000000000000000000000000000000000000000000           

      600000000000                                                   

      609900000000                                                   

      4100009171841007326                                             

      /font[/quote]Each new record starts when a number appears in the first column.  Depending on the first 4 digits of this line, there may or may not be more lines below (all of which are indented one spot).

       

      The sample report I'm using has 85 records (there is 315 similar reports I'm going to load once I figure out how to get the model working correctly.)  However, I've tried detail and append, using the preceding string as a blank for the successive lines, and several other items.  I never get the correct number of records returned.

       

      I don't need to trap any real fields (I'm going to break out each line afterwards because the data is in different spots depending on those first 4 digits.)

       

      Can anyone point me in a direction?

       

      Thanks,

      Gene

       

      [size="1"][ September 03, 2003, 08:47 AM: Message edited by: Gene ][/size]

        • Multi Line Trap Confusion
          Grant Perkins

          Gene,

           

          C&P of your sample, making sure that the spaces exist at the start of the lines as you indicate (they got lost in the C&P somewhere!) and then trying a few things, got me to this. It seems to be what you are seeking. (Using 5.02)

           

          Your detail template is for the non-indented lines. So I simply set the template to pick lines with a numeric at column 1 and then the whole line for the data.

           

          Then select the same line as above plus one below it for the next template (I used lines 1 and 2 from the sample). Use the same selection criterion for the template but ignore line 1 and select the whole possible width (except the first space column) of the second line as the field.

           

          Go to Field Definition>>Options and set the "Start Field On" option to "Preceding string" with the single blank, and the "End Field On" option to  "minimum action only". Note that on the sample any of the options except Line Count will probably be OK.

           

          Then identify the template as a FOOTER.

           

          That seems to do it.

           

          Then you have the simple job of splitting the fields into the appropriate fields!

           

          Hope it works for you the way the way it looks like it works for me.

           

          Good luck.

           

          Grant

           

           

            Originally posted by Gene:

          I'm having issues.

           

          I've got a report where sometimes the record is one line long, other times it may be 6 or 7 lines long.

          I've gone thru multiple other posts on the forum regarding multiple line traps, however, I'm not getting the data to look correct after trapping.

           

          My report resembles this:

           

          [font="courier"]5000009161204443790061007326                                    

          5500    696994354003100097500200994354310002               0   

          5300000019500000000000000000                                   

          5970000000000000000000000000000000000000000000000000           

          600000001950                                                   

          609900000000                                                   

          4100009163408007326                                             

          4000009163412000023                                             

          540000900023163436    696994313150                              

          540000900023163522    696994356150                              

          4100009163525000023                                             

          4000009163555007326                                             

          5000009164309443820061007326                                    

          5500    693194328000500000000200994328050002               0   

          5300000000000000000000000000                                   

          5970000000000000000000000000000000000000000000000000           

          600000000000                                                   

          609900000000                                                   

          4100009171841007326                                             

          /font[/quote]Each new record starts when a number appears in the first column.  Depending on the first 4 digits of this line, there may or may not be more lines below (all of which are indented one spot).

           

          The sample report I'm using has 85 records (there is 315 similar reports I'm going to load once I figure out how to get the model working correctly.)  However, I've tried detail and append, using the preceding string as a blank for the successive lines, and several other items.  I never get the correct number of records returned.

           

          I don't need to trap any real fields (I'm going to break out each line afterwards because the data is in different spots depending on those first 4 digits.)

           

          Can anyone point me in a direction?

           

          Thanks,

          Gene /b[/quote]

           

          [size="1"][ September 03, 2003, 01:18 PM: Message edited by: Grant Perkins ][/size]

          • Multi Line Trap Confusion
            Gene _

            Grant:

             

            Thanks for the help, I was having I was trying to make one huge detail, and it wasn't working very well.

             

            Thank you very much.

            • Multi Line Trap Confusion
              Gene _

              Grant can I bother you one more time?

               

              We realized that when going thru the reports, if the transaction involved a credit card, the info appears above the transaction.

              The CC line always begins with 3100.

               

              I tried to append this to the other detail info already grabbed, however some records do not have the field to append, and the append fills in to too many records.

               

              I had set the detail and footer trap to look for numerics in the first 5 positions, thereby eliminating the credit card lines, and set a trap for the 3100 to grab the credit card lines.

               

              Am I making this more complicated than I need to?

               

              New Sample

              [font="courier"]3100>B:G7gZ&RPA.8L(>|}}}01596100000975                          

              5000009221521444240081000023                                    

              5500    693194330003100097500100994330310001               0   

              5300000009750000000000000000                                   

              5970000000000000000000000000000000000000000000000000           

              600100000975                                                   

              6100>B:G7gZ&RPA.8L(>|}}}A00000000                              

              6150S0159610101NAME/NAME                  00000000              

              609900000000                                                   

              3100=A9F9bX)UT=2;J%=~}|}      00001950                          

              5200009221553444250071000023                                    

              5500    6931943300031000975002                             0   

              5300000019500000000000000000                                   

              5970000000000000000000000000000000000000000000000000           

              600100001950                                                   

              6100=A9F9bX)UT=2;J%=~}|}A00000000                              

              6150S      0101NAME/NAME                  00000000              

              5501009222252    6931943300030000000001000023                    /font[/quote]I desperately need to take an advanced training class   smile.gif[/img]

              • Multi Line Trap Confusion
                Grant Perkins

                Gene,

                 

                Certainly does complicate things a bit - not sure if the forum allows subsidiary questions of this nature!     

                 

                One 'dirty' route would be to simply append the data for the 3100 CC line and then keep it or eliminate it using a formula field to compare if with some other data. For example, looking at the new sample, seems like every CC transcation MUST HAVE something in the 'footer' field. So we could compare on that and exclude the CC data if the footer is empty.

                 

                However I don't think that will cover all the situations you will find in the data so there may need to be several such comparisons.

                 

                I'm going to play around some more and I also have a look back through the archives 'cos something is nagging me about seeing a good solution to a very similar problem a while back.

                 

                If all else fails (and depending upon which version of Monarch you are using) it may be that we have to look at the good old "2 stage" solution using a lookup table (either external if you have V5 or above Pro version) or internal if you have standard.

                 

                For that I think, off the top of my head, I would look to extract the 3100 lines into a separate table but I would need to relate them to a transaction reference number or something so that when we run the second stage (what you had before the new CC lines were recognised as an issue) you would then be able to read the lookup table and append the CC information to each record that had a relevant entry.

                 

                A few options and questions. I'll hope to have something for you tomorrow, one way or the other.

                 

                Or maybe some of our friends on the board will beat me to it?

                 

                All the best.

                 

                Grant

                 

                  Originally posted by Gene:

                Grant can I bother you one more time?

                 

                We realized that when going thru the reports, if the transaction involved a credit card, the info appears above the transaction.

                The CC line always begins with 3100.

                 

                I tried to append this to the other detail info already grabbed, however some records do not have the field to append, and the append fills in to too many records.

                 

                I had set the detail and footer trap to look for numerics in the first 5 positions, thereby eliminating the credit card lines, and set a trap for the 3100 to grab the credit card lines.

                 

                Am I making this more complicated than I need to?

                 

                New Sample

                [font="courier"]3100>B:G7gZ&RPA.8L(>|}}}01596100000975                          

                5000009221521444240081000023                                    

                5500    693194330003100097500100994330310001               0   

                5300000009750000000000000000                                   

                5970000000000000000000000000000000000000000000000000           

                600100000975                                                   

                6100>B:G7gZ&RPA.8L(>|}}}A00000000                              

                6150S0159610101NAME/NAME                  00000000              

                609900000000                                                   

                3100=A9F9bX)UT=2;J%=~}|}      00001950                          

                5200009221553444250071000023                                    

                5500    6931943300031000975002                             0   

                5300000019500000000000000000                                   

                5970000000000000000000000000000000000000000000000000           

                600100001950                                                   

                6100=A9F9bX)UT=2;J%=~}|}A00000000                              

                6150S      0101NAME/NAME                  00000000              

                5501009222252    6931943300030000000001000023                    /font[/quote]I desperately need to take an advanced training class     smile.gif[/img]   /b[/quote]

                 

                [size="1"][ September 05, 2003, 11:00 AM: Message edited by: Grant Perkins ][/size]

                • Multi Line Trap Confusion
                  Grant Perkins

                  Gene,

                   

                  Here is the 'rough and ready' solution to the Credit Card line problem. I make no claims of elegance.

                   

                  Go for your solution that over-reports the association of the Credit Card Transaction details by associating it with additional erroneous lines.

                   

                  The following section can probably be reduced to a single formula creating a single new data field. For simplicity of explanation I will present it as multiple fields and calculations. This has the merit of flexibility and easier reading, especially when generating a new model. Apologies if I appear to have described it overly simplistically. I think you may well be quite able to resolve this yourself but I have attempted to describe it in a way that others with less knowledge could also have a reasonable chance of following. (Did I succeed?)

                   

                  Looking at the sample it seems that the 3100 line string is repeated in part down in the lines that we are extracting on the footer template. FOR THIS SOLUTION TO WORK THE ABOVE MUST ALWAYS BE TRUE AT LEAST FOR SOME PART OF THE LINE (AND THE STRING MUST BE UNIQUE ON THE LINE).

                   

                  What we need to do is identify whether the 3100 line string does actually appear in the 'footer detail field' somewhere.

                   

                  If the 'footer data' line(s) field  is called SUBDATA and the Credit Card Line field is called CCLINE, the following formula will identify whether there is a match for part of the CCLINE field somewhere in the SUBDATA field. It will return either 0 (Zero, possibly displayed as blank) if there is no match OR the character position where the match actually begins in the SUBDATA string. You may already be using something similar to split up your collected 'footer' lines into the data fields you require.

                   

                  if((Substr(CCLINE,1,12))<>" ",Instr((Substr(CCLINE,1,12)),SUBDATA),0)

                   

                  OK, so the explanation is;

                   

                  If the first 12 characters of the field CCLINE are not Blank, then use the INSTR function to return the character position number of the start of the matching string if it is matched but if CCLINE is an empty field return the value 0 (zero). The INSTR function may not find a match in which case it will also return a value of zero. (In this case the field nees to be defined as a numeric field. Monarch may well default to a Character field so take care as the formula will appear to fail.

                   

                  So we now have a new calculated field which will either be blank (or zero) or have a number in it. The fields with numbers mean those are genuine Credit Card transaction records. So you can now correctly flag those records that are credit card transactions.  I don't know what you may want to do with them but one simple way of creating a calculated field that is populated with the original information (contents of the CCLINE row for example) might be;

                   

                  Assume we called the new calculated field from above  IS_CCTRANS

                   

                  if(IS_CCTrans>0,CCLINE,"")

                   

                  Very simply, this IF function checks to see if the new field has a value greater than 0, if it does the additional new field is given the value fron the CCLINE field but if it the value is zero or less the new field will be left blank.

                   

                  If you feel like playing with nested IF functions it should be possible to insert the first formula above into the second one to complete the step in one transaction.

                   

                  if((if((Substr(CCLINE,1,12))<>" ",Instr((Substr(CCLINE,1,12)),SUBDATA),0))>0,CCLINE,"")

                   

                  should be quite close to getting it in one.

                   

                  I hope this helps you to move foward.

                   

                  Grant

                   

                  PS - I will still have a look through the archives to seek a better answer.

                   

                  [size="1"][ September 05, 2003, 11:12 AM: Message edited by: Grant Perkins ][/size]

                  • Multi Line Trap Confusion
                    Gene _

                    Grant,

                    That worked great!

                     

                    Thank you very much.

                     

                    Gene

                    • Multi Line Trap Confusion
                      Gene _

                      Working on something else and have bumped into this....

                       

                      If there are empty spaces at the end of an indivdual line, Monarch doesn't pick them up.

                      Is there a way to force Monarch to keep these extra spaces?

                       

                      So where the report shows:

                       

                      [font="courier"]5500    693194108000100095000205094108010002             

                      5300000019000000000000000000                                   

                      5970000000000000000000000000000000000000000000000000           

                      600000001900                                                    /font[/quote]Monarch grabs:

                       

                      [font="courier"]5500    693194108000100095000205094108010002 5300000019000000000000000000 5970000000000000000000000000000000000000000000000000 600000001900      /font[/quote]Do I need a change a setting, I would like to keep those blank spaces in there!

                       

                      Thanks,

                      Gene

                      • Multi Line Trap Confusion
                        Grant Perkins

                        Gene,

                         

                        This sounds like pretty much the same things as  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000289"]bickle[/url] has raised. It may look different but it seems very similar to me.

                         

                        Certainly makes slice and dice of a field based on character position a bit more complex than looking at the source fiel data structure!

                         

                        Looking for further inspiration ...

                         

                        Grant

                         

                          Originally posted by Gene:

                        Working on something else and have bumped into this....

                         

                        If there are empty spaces at the end of an indivdual line, Monarch doesn't pick them up.

                        Is there a way to force Monarch to keep these extra spaces?

                         

                        So where the report shows:

                         

                        [font="courier"]5500    693194108000100095000205094108010002             

                        5300000019000000000000000000                                   

                        5970000000000000000000000000000000000000000000000000           

                        600000001900                                                    /font[/quote]Monarch grabs:

                         

                        [font="courier"]5500    693194108000100095000205094108010002 5300000019000000000000000000 5970000000000000000000000000000000000000000000000000 600000001900      /font[/quote]Do I need a change a setting, I would like to keep those blank spaces in there!

                         

                        Thanks,

                        Gene /b[/quote]

                         

                        [size="1"][ May 19, 2006, 12:18 PM: Message edited by: Todd Niemi ][/size]

                        • Multi Line Trap Confusion
                          Gene _

                          Certainly does look similar.  Not quite sure how I missed his post.

                          I think the only difference is, I'm using a mult line trap, so trying to add the trailing spaces based on the length of the field would add what spaces are at the very end of the field.

                          However, I also want to keep the correct number of spaces in the middle as well!

                           

                          A neato little setting in Monarch would be nice.

                           

                          Future release maybe?

                           

                          [size="1"][ September 26, 2003, 10:00 AM: Message edited by: Gene ][/size]