8 Replies Latest reply: May 15, 2014 10:06 AM by Grant Perkins RSS

    Floating Trap Help

    herzberg _

      Can you help me find a way to extract the two columns of numbers from this report?

       

      <conv:accountID>234564524305</conv:accountID><conv:accountType>IL|NEW</conv:accountType><conv:amount>-298.25</conv:amount>

      <conv:accountID>4300094</conv:accountID><conv:accountType>IL|NEW</conv:accountType><conv:amount>2224.81</conv:amount>

      <conv:accountID>12354789692</conv:accountID><conv:accountType>IL|NEW</conv:accountType><conv:amount>-1928.25</conv:amount>

      <conv:accountID>944522101</conv:accountID><conv:accountType>IL|NEW</conv:accountType><conv:amount>24.81</conv:amount>[/CODE]

       

      To get a report that looks like this:

       

      ID              COST

      234564524305   -298.25

      4300094        2224.81

      12354789692   -1928.25

      944522101        24.81[/CODE]

       

       

       

      Thanks

      JC

        • Floating Trap Help
          Data Kruncher

          Hi JC and welcome to the forum.

           

          Instead of fighting with a floating trap (and probably losing) I'd take a slice and dice approach to this one.

           

          Capture the entire text line as a single field. Name the field simply as "A".

           

          Now in the Table window, create two calculated fields.

           

          The first is a Character field with this formula:

          LSplit(LSplit(A,2,"accountID>",2),2,"<",1)[/CODE]

           

          The second is a Numeric field with 2 decimals with this formula:

          Val(LSplit(LSplit(A,2,"amount>",2),2,"<",1))[/CODE]

           

          Now hide your "A" field.

           

          HTH,

          Kruncher

            • Floating Trap Help
              Grant Perkins

              Hi JC,

               

              Despite Kruncher's valid caution and excellent suggestion, for once in a post to the forum this challenge is /Ibased on data for which the floating trap was intended to be used! Hooray! And thank you!

               

              You need to pick a suitable sample line - the sample is important for floating traps since the traps have to match the selected sample line in order for auto adjustments to both position and field size to be calculated.

               

              In this case any line will do since the fields you want as populated in all lines.

               

              Load the file and trap on the first line

               

              For the first number field (Account ID) use something like

               

               

              ID>         <

               

              /code

               

              where the > and < exactly match the characters before the start and after the last position of the field as it appears on that line.

               

              'Paint' the field for the full width of the account ID number which should fit exactly between the defining > and < symbols.

               

              Do the same thing on the same line for the AMOUNT field using

               

              amount>       <

              /code

               

              Set the trap to be a floating trap if you have not already done so.

               

              You should now see the data you want highlighted no matter where is positioned on the line and how wide the field is.

               

              TO be sure of avoiding any future data size problems I tend to try to find out how large the fields may be allowed to be - they may be bigger than the samples for example. Also whether they are always consistent for character or numeric or date and set the field definition accordingly.

               

              There might be occasions where creating a mock up sample line that reflects the maximum field sizes possible is a good idea in order to help the floating trap do its work. However in this case it looks like things should work without a problem based on the sample lines presented.

               

              I hope this helps.

               

              Grant

              • Floating Trap Help
                herzberg _

                Works fantastic.  You are the best.

                 

                Thanks

                JC

                • Floating Trap Help
                  Nick Osdale-Popa

                  The first is a Character field with this formula:

                  LSplit(LSplit(A,2,"accountID>",2),2,"<",1)[/CODE]

                   

                  The second is a Numeric field with 2 decimals with this formula:

                  Val(LSplit(LSplit(A,2,"amount>",2),2,"<",1))[/CODE]

                   

                  HTH,

                  Kruncher[/QUOTE]

                   

                  OK, I'm going to scold you for using those LSplits!

                  Alternatively, you can use the extract function

                  Extract(A,"accountID>","<")[/CODE]

                   

                  The second is a Numeric field with 2 decimals with this formula:

                  Val(Extract(A,"amount>","<"))[/CODE]

                   

                  This function is available in V8.

                    • Floating Trap Help
                      Data Kruncher

                      Yeah, I know... I tend to forget about Extract(). :o

                       

                      Olly used it earlier today, and I immediately thought back to this post, but had no time to login and post as it was time to drive to the office.

                       

                      I promise to do better.

                       

                      Maybe I'll start a Monarch website to help myself remember these functions a little better... nah, that won't work. :rolleyes:

                        • Floating Trap Help
                          Grant Perkins

                          Hmm. People, I still have to wonder why you don't just use the Floating trap  for exactly the purpose for which it was intended (so far as I can see ....)

                           

                           

                          :confused:

                           

                           

                          Grant

                            • Floating Trap Help
                              Data Kruncher

                              I [B]suspect[/B][/I] that I've been conditioned by the number of posts here wherein the OP is attempting to use floating traps and it just never works for their sample, so we find other routes.

                               

                              Now I just go straight to other routes out of, hmm, comfort? Habit?

                               

                              Either way, you're right of course. Maybe that web site would be a good idea after all...

                                • Floating Trap Help
                                  Grant Perkins

                                  I [B]suspect[/B][/I] that I've been conditioned by the number of posts here wherein the OP is attempting to use floating traps and it just never works for their sample, so we find other routes.

                                   

                                  /quote

                                   

                                  Happens to us all from time to time. I would guess, what, one question a year that might be a relatively straightforward use of the Floating trap even though not immediately obvious how to get it to work?

                                   

                                  Easily missed in the excitement of applying a multiple scenario solution with wider applications.

                                   

                                  Still, it's nice to give the less discussed features an airing from time to time.

                                   

                                  About that web site ...

                                   

                                   

                                  Grant