5 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    How could i trap this

      Hello everybody,

       

      i tried several traps but nothing really worked.

      [font="courier"]     13 E233187 POLAT, YUSUF  1 FB2 A    10  2 ERW        789.20 EUR      789.20 EUR

                                                   1 K. 14J       0.00 EUR        0.00 EUR

                                                   1 K. 14J     116.80 EUR      116.80 EUR

           14 E232963 MACHAUER, BAR 1 DZI A    10  2 ERW        789.20 EUR      789.20 EUR

                                                   1 K. 14J       0.00 EUR        0.00 EUR

           15 E235541 VOGEL, ALEXAN 1 DZ  A    12  2 ERW        972.64 EUR      972.64 EUR

           16 E235672 SARIKAYA, ABD 1 F22 A    11  2 ERW        880.92 EUR      880.92 EUR

                                                   1 K.  4J       0.00 EUR        0.00 EUR

                                                   1 K.  6J     128.48 EUR      128.48 EUR

           17 E235697 AKHAN, KAROLA 1 F22 A    14  2 ERW       1156.08 EUR     1156.08 EUR

                                                   1 K.  4J       0.00 EUR        0.00 EUR

                                                   1 K.  6J     163.52 EUR      163.52 EUR

           18 E235708 SIEBERT, JOCH 1 FB2 A    12  2 ERW        972.64 EUR      972.64 EUR

                                                   1 K.  4J       0.00 EUR        0.00 EUR

                                                   1 K. 14J     140.16 EUR      140.16 EUR   /font[/quote]As you see every instance is starting with a running number like:13, 14, 15 ...

       

      How could i trap the information without this number ?

      [font="courier"]                                             1 K.  4J       0.00 EUR        0.00 EUR

                                                   1 K.  6J     128.48 EUR      128.48 EUR  /font[/quote]and match it to the 7 digits ordernumber in the second column like E233187[/b].

       

      Second Problem is that e.g. line 15 doesn`t have this extra information.

       

      What i`m expecting is somthing like this:

      [font="courier"]     E233187 POLAT, YUSUF  1 FB2 A 10  2 ERW 789.20 EUR 1 K. 14J 0.00 EUR 1 K. 14J 116.80 EUR

           E232963 MACHAUER, BAR 1 DZI A 10  2 ERW 789.20 EUR 1 K. 14J 0.00 EUR

           E235541 VOGEL, ALEXAN 1 DZ  A 12  2 ERW 972.64 EUR 1 K.  4J 0.00 EUR 1 K.  6J 128.48 EUR 1 K. 14J 140.16 EUR                                      

           E235672 SARIKAYA, ABD 1 F22 A 11  2 ERW 880.92 EUR   /font[/quote]I`m using Monarch V6 and V8 Pro.

       

      Thanks for any answer in advance.

       

      Rolf

        • How could i trap this
          Mike Urbonas

          Sure Rolf, there are a few ways this can be done - here is one way:

           

          (1) Create a Detail trap that only captures the report rows with no running number, etc.  You can do this a few different ways:

           

          -- The report rows you included in your post have the characters "K." in the same position (the other rows do not). If the entire report looks the same as this, then a Detail trap that looks for the "K." will work.

           

          -- Another option that will definitely work is to define your Detail trap using a decimal point "." in one of the positions where a "." appears for all of the rows, PLUS use a "Blank" trap on the position where the first digit of the running number will appear.  ("Blank trap" is created using the Blank ["Beta"] button).

           

          Either of these Detail traps should capture the data you want, while ignoring the report rows that begin with a running number.

           

          (2) Now highlight one of the report rows with a running number and do an Append trap, using a Numeric wildcard trap on the position of the first digit of the running number.

           

          That should do it!

          Mike

          • How could i trap this

            Hi Mike,

             

            thank you for your answer, but this is more or less what i already did. The result looks like this:

            [font="courier"]E233187     POLAT, YUSUF     1 FB2     A     10     2     ERW     789     1     14J     0

            E233187     POLAT, YUSUF     1 FB2     A     10     2     ERW     789     1     14J     116,8

            E232963     MACHAUER, BAR     1 DZI     A     10     2     ERW     789     1     14J     0

            E235672     SARIKAYA, ABD     1 F22     A     11     2     ERW     881     1      4J     0

            E235672     SARIKAYA, ABD     1 F22     A     11     2     ERW     881     1      6J     128,48

            E235697     AKHAN, KAROLA     1 F22     A     14     2     ERW     1156     1      4J     0

            E235697     AKHAN, KAROLA     1 F22     A     14     2     ERW     1156     1      6J     163,52

            E235708     SIEBERT, JOCH     1 FB2     A     12     2     ERW     973     1      4J     0

            E235708     SIEBERT, JOCH     1 FB2     A     12     2     ERW     973     1     14J     140,16

              /font[/quote]As you see, i`ve got more than one row for eg. Ordernumber E233187. And running number 15 is totally missing.   

             

            I think it`s more difficult as it look like on the first view. Any more ideas ?

             

            Thanks in advance.

             

            Regards

            Rolf

            • How could i trap this
              Grant Perkins

              Hi Rolf,

               

              If you trap on the left side of each main record (the names and that part of the data) you could then create a single field for everything on the right

              [font="courier"]2 ERW        789.20 EUR      789.20 EUR[/font][/quote]and make this a multi-line field so that it will pick up 1 or 2 or 3  or however many lines there are into a single field.

               

              To display that field on a single line you either need to make the display very wide (the field may need to be a Memo field if the total number of characters exceeds 254) or split the line into separate 'calculated' fields for the output.

               

              That would be easier to do using Version 8 working with the TextLine function. However if you have to make this work in version 6 it should be possible to come up with a formula that breaks the line into component fields because the length of each block of data is fixed at somewhere around 40 characters (that is a quick visual estimate not an accurate assessment!)

               

              The only problem with the concept of the multi-line and split idea is that there will be an unknown number of lines between 1 and ??????.

               

              If you need to split into separate fields you will need to define enough POSSIBLE calculated fieds to hold the information for the record with the most possible lines. (Or make a field wide enough to display x lines or 40 - or more - characters. Doing that might result in something that is not very useful.)

               

              Does this help at all?

               

               

              Grant

              • How could i trap this

                Hi Grant,

                 

                that was a very cool tip. That worked in a way i can deal with it. Thank you so far for your help.

                 

                What i don`t understand completely is this:

                 

                [font="courier"] ...or split the line into separate 'calculated' fields for the output /font[/quote]Is this something i can do in the trap ? That needed to be done, to split the informations i`ve got now in 1 column in single ones.

                 

                Regards

                Rolf

                • How could i trap this
                  Grant Perkins

                  Originally posted by starpower:

                  What i don`t understand completely is this:

                   

                  [font="courier"] ...or split the line into separate 'calculated' fields for the output /font[/quote]Is this something i can do in the trap ? That needed to be done, to split the informations i`ve got now in 1 column in single ones.

                   

                  /b[/quote]Hi Rolf,

                   

                  It is good to know the suggestion helped.

                   

                  If you want to split the multi-line field into separate columns do it using calculated fields from the TABLE window. (It is not something that can be done in the template. There are a couple of reasons why not, although for some reports some times there it may be possible to create a satisfactory result with some careful work. Not something I would recommend normally.)

                   

                  The question then becomes how do you split the data and that depends upon what you want to split.

                   

                  Your suggested output format suggests that maybe each LINE could be considered as a single data 'field' with its own column. The simple way to do that would be to use the TEXTLINE function in Version 8. To keep it simple you would need to know how many rows would be the maximum number to appear (and so how many columns(fields) you need to create.). If that number is not constant OR the maximum possible is large that may be a problem.

                   

                  In fact the potential for a large number of lines for those 'fields' is likely to be a challenge whichever way you look at the problem.

                   

                  If you needs a solution compatible with V6 then you cannot use TextLine. However the lines are of a known length and so a long field can be split successfully using the SUBSTR function to process the long field extracted and then specifying a different start point for each calculated field required.

                   

                  If you need to break the report lines into separate fields there are a few ways to do that but the most consistent in this case is probably to use the SUBSTR function again. You could SUBSTR the previously separated fields (either the TextLine of SUBSTR fields from above) or take the one long field originally extracted and work out the format and then set the start and length fields for each component part of the data in the field.

                   

                  If that last idea seems interesting to you there is also another option!  :confused: 

                   

                  You could look at each of the fields at the end of the line as separate multi-line extractions and then use TextLine or SUBSTR functions (or even perhaps the SLPIT functions (LSPLIT, RSPLIT)  to separate the fields from there.

                   

                  It really just depends on what you want to see when the extraction is complete and how the data need to be separated.

                   

                  Mike's solution offers easier analysis for some tasks - but may not be exactly what you want to achieve at the moment for this analysis work.

                   

                  If you can add some more requirements to the way you want to see your output based on this response I will make some specific suggestions for you to investigate.

                   

                  HTH.

                   

                   

                  Grant