6 Replies Latest reply: May 15, 2014 9:51 AM by Nigel Winton RSS

    Generic reference for a number?

    Hobbes _

      Hi,

      Is there a way to generically reference a number or letter?

       

      I have a string that I am trying to extract bits of information from, but there is only one phrase that is the same in all of the records I need.  The problem is that I need to extract roughly three separate items both before and after this phrase.  The format of the lines are the same, but what is actually says differs with each record.  Here's an example of what I'm looking at (each record is on one line):

       

      Name of Customer 09/89 Purchase 900 BIT @ 85.21 Comment .47 Ball 55.77

       

      Customer's Name 10/74 Sale 650 BIT @ 7.56 Ball 125.78

       

      Different Customer 12/03 Sale 5 BIT @ 120.30 Comment .69

       

      I need to extract the part up to the "00/00", the "00/00", the "Purchase or Sale", the "900, 600, 5", the number after the @, the number after "Comment", and the number after "Ball".

       

      I can grab it with a left or right split, but I don't know how to generically reference to look for a number or a letter (like you can do when creating a model in the report view).

       

      Thanks for your help!

       

      I'm using Monarch V7.01 Pro.

        • Generic reference for a number?
          Nigel Winton

          Hobbes

          Try lookin at these calculated fields, you should be able to carry on withthe theme and get all of the fields you need.

          You need to trap the whole line as on field and call it orig. Posit bcomes the lock for the extracting, and the rest is fun.

           

          instr("/",orig)  Use this on to fix a point

           

          substr(orig,1,posit-3) This should give you the name.

           

           

          substr(orig,posit-2,5) This will give you the period

           

          if(substr(orig,posit4,1)="P","Purchase",if(substr(orig,posit4,1)="S","Sale",""))

          And this one will do the Purchase or sale field

           

           

          Enjoy

           

          Nigel

          • Generic reference for a number?
            Nigel Winton

            Sorry should have said to name the first calculated field posit.  :rolleyes: 

             

            Nigel

            • Generic reference for a number?
              Hobbes _

              Thank you Nigel for the help!  The example you gave me almost works perfectly.  I have discovered that one of my Name fields includes a "/" in the name.  I like the idea of using the "/" as a position lock, but is there a way to reference the number before or after it?

               

              Thanks!

              • Generic reference for a number?
                Grant Perkins

                Hobbes,

                 

                Here is something else to experiment with using the FLOATING TRAP and a few ideas along the same lines as Nigel's suggestion.

                 

                Firstly, to make life easier working with the floating trap, create a DUMMY line where each part of the line is as wide as it is ever likely to be in a real report.

                 

                For example

                [font="courier"]à                ßÑ    ßà      ßÑ    ß      Ñ      ßà                      

                 

                Different Customer 12/03 Purchase 12345 BIT @ 9999.99 Comment 999.99 Ball 1000.00[/font][/quote]or whatever is appropriate. Use this as the SAMPLE text in your template.

                 

                For the above line try the following traps.

                 

                Alpha first character (to eliminate blank lines).

                 

                Blank traps for the SECOND space (end of the customer name) and NUMERIC for the number immediately following.

                 

                Blank trap at the end of the 12/03 with an ALPHA trap immediately following for the start of the word PURCHASE.

                 

                Blank trap at the end of PURCHASE followed by a NUMERIC trap for 12345. Blank trap at the end of the number.

                 

                NUMERIC trap at the start of the 120.30 number, BLANK trap at the end immediately followed by an ALPHA trap for the C in COMMENT.

                 

                If there were always at least 2 fields there you could also set a BLANK trap after 999.99 immediately followed by an ALPHA trap for BALL. However in this case it will produce a trap that will skip some lines where only one field exists.

                 

                You should now be able to 'paint' all the fields you require EXCEPT that I think you will need to create a single field to include both "Comment 999.99" and "Ball 1000.00" since it is not possible to easily identify what the last 2 values are for. Preceding string functionality is not available when floating traps are used. However you should be able to create a couple of calculated fields and do some conditional processing and splitting to extract the values you require to the right columns.

                 

                For example, if we call those fields VALUES and you want to create a field for the value of COMMENT.

                 

                For Example a calculated NUMERIC field set to 2 decimal places with the following formula would, in V7, give the values for "Comment".

                 

                Val(Extract(VALUES,"COMMENT",))

                 

                and for BALL

                 

                Val(Extract(VALUES,"BALL",))

                 

                would work well.

                 

                LSPLIT, RSPLIT, IF(), LEFT, RIGHT, INSTR and SUBSTR might also be valuable functions to consider, especially for users of earlier versions who may not have the EXTRACT function available. Of course the same comment goes for the entire suggestion if you don't have the Floating Trap feature available!

                 

                What do you think? Does it work for your real report? It may not.

                 

                Grant

                 

                [size="1"][ September 09, 2004, 06:47 PM: Message edited by: Grant Perkins ][/size]

                • Generic reference for a number?
                  Nigel Winton

                  Hobbes

                  Try using this one,

                  substr(orig,posit-2,5)

                  All you need to do is vary the plus or minus to the posit number and voila, that gives you the start position for your string to use substr on.

                   

                   

                  Nigel

                  • Generic reference for a number?
                    Nigel Winton

                    Hobbes

                    if you are stuck with a / in the customer name you can always try using the BIT word  or Purchase or Sale instead of the /. All of these will give you a posit number that you can work from. You could even use a combination of 2 or 3 posits.

                     

                    Nigel