3 Replies Latest reply: May 15, 2014 9:56 AM by Hobbes _ RSS

    Between function in filters?

    Hobbes _

      Hi! 

       

      Is it possible to create a filter that will trap information in a line much like I could do with a model? 

       

      I have a line of data that I extract from a report and it can include about 10 variations of information format (so I can't set a model to extract only bits of it because I would be missing more records than I would extract).  I am trying to pull a price from this transaction line.  Here is an example of my data (it's all in one field, one line):

       

      ABC COMPANY GOT AT 123.45 87,555.32 20.47 PRICE .69 FEE MORE RANDOM INFORMATION

       

      The information that would be constant is GOT AT, PRICE, and FEE.  There are not always three values in between GOT AT and PRICE, but the value I need right now is the 20.47.  I tried setting an extract filter, but the value I need is before PRICE (which is the most constant piece of information for this extraction).  I've tried Extract(," "," PRICE") but it returns everything before " PRICE".  How do I get it to read the value that is placed here:   (blank)[/i] 00000.00 (blank)[/i] PRICE.  I want it to pull the value between the blanks.  The value I want will always be a number, but might have a decimal point or comma. 

       

      I hope this makes sense. 

      Any ideas?

       

      Thanks!!!

      Hobbes   tongue.gif[/img]

        • Between function in filters?
          Nick Osdale-Popa

          Are you looking for a filter or a calculated field?

          For a calculated field, I came up with this:

          (Where holds your data)

          [font="courier"]RSplit(Trim(Extract(,"AT","PRICE")),2," ",1)[/font][/quote]Edit: If you need it to be an actual number, just use the Val() function around the above formula.

           

          [size="1"][ August 09, 2004, 02:09 PM: Message edited by: Nick Osdale-Popa ][/size]

          • Between function in filters?
            Grant Perkins

            Could be tricky with lots of unpredictable random text BUT maybe a little Voodoo Monarch will help.

             

            Using your Extract idea you could try extracting anything that appears between GOT and PRICE (if GOT always exists. If you them TRIM the resulting field to remove any trailing spaces you should be able to RSPLIT that result based on space being the separator and take the first section (i.e. the RIGHTMOST string using RPLIT) for the data your require. I recommend you run each stage to a specifid calculated field as you develop the expression and then combine the formulae at the end into a one step process. It can be easier to follow what is happening.

             

            As an alternative you could use INSTR to find the start position of PRICE. If you then have a reasonable idea of the maximum length of the field you want, say 15, you could use SUBSTR to extract whatever appear between the - 16 for 15 (adjust as neccesary for the real format of your report and possibly to remove training spaces and so on.

             

            From that result you should be able to RSPLIT to separate the value required once again.

             

            There are probably some variations to this approach but see how you get on with these ideas first. If something stops them working let us know and I am sure other ideas will be forthcoming!

             

            Grant

             

            Originally posted by Hobbes:

            Hi! 

             

            Is it possible to create a filter that will trap information in a line much like I could do with a model? 

             

            I have a line of data that I extract from a report and it can include about 10 variations of information format (so I can't set a model to extract only bits of it because I would be missing more records than I would extract).  I am trying to pull a price from this transaction line.  Here is an example of my data (it's all in one field, one line):

             

            ABC COMPANY GOT AT 123.45 87,555.32 20.47 PRICE .69 FEE MORE RANDOM INFORMATION

             

            The information that would be constant is GOT AT, PRICE, and FEE.  There are not always three values in between GOT AT and PRICE, but the value I need right now is the 20.47.  I tried setting an extract filter, but the value I need is before PRICE (which is the most constant piece of information for this extraction).  I've tried Extract(," "," PRICE") but it returns everything before " PRICE".  How do I get it to read the value that is placed here:   (blank)[/i] 00000.00 (blank)[/i] PRICE.  I want it to pull the value between the blanks.  The value I want will always be a number, but might have a decimal point or comma. 

             

            I hope this makes sense. 

            Any ideas?

             

            Thanks!!!

            Hobbes    tongue.gif[/img]  /b[/quote]

            • Between function in filters?
              Hobbes _

              WONDERFUL!  Thank you Nick and Grant...it works perfectly!   [img]tongue.gif[/img]