3 Replies Latest reply: May 15, 2014 10:03 AM by Data Kruncher RSS

    Find last occurance in field

    debijo _

      I have a rather long field I am trying to parse.  I need to pull out the value following the first and last occurance of a particular string.  I did the first occurance: 

       

      lsplit(trim(lsplit(substr(detail1,instr("Part Number",detail1),len(detail1)),2,":",2)),2," ",1)

       

      This uses the instr function to find the first instance of "Part Number".  Now I need to find the last occurance.  I cannot seem to find an equivalent instr function that parses from right to left.  There could be any number of occurances of "Part Number" in this single field.  Any ideas how I can find the last one?

       

      Thanks in advance!

      Debi

        • Find last occurance in field
          Data Kruncher

          Hi Debi,

           

          Can I ask what your original detail1 field typically looks like?

           

          I have an idea for an alternate approach, but it's a bit contigent upon what you're starting with, and what you're trying to get out of it at the end.

           

          And rather that mislead you, I won't let the cat out of the bag just yet.

            • Find last occurance in field
              debijo _

              Something similar to the following.  The file is very unpredictable, the only thing for sure is the order number.  I want to see the first and last part # of each order and the location (if any) associated with the part.  I was trying to do part # as the detail and use appends and footers for order and location.  However, I couldn't tell if it was the first or last part number (those are the only 2 I need). 

               

              So, I pull all this in a memo field, which stops at the next occurance of Order Number.  Now I'm trying to parse though the detail and pull out what I need.  I just need to go from right to left....  I know there are other ways to do this, however, this one detail line approach is working very well for the rest of the model.....

               

               

              Order Number......:  C0453

              Customer Number...:  9876-001

               

              Part Number.......:  123-456

              Description.......:  Widget 123 in green

              Quantity..........:  10

              Instructions......:  Pack separately

               

              Part Number.......:  123-999

              Description.......:  Widget 123 in blue

              Description.......:  with decorative edges

              Quantity..........:  11

              Location..........:  West warehouse

               

              Part Number.......:  223-456

              Description.......:  Widget 223 in green

              Quantity..........:  19

               

              Customer Number...:  9876-001

               

              Part Number.......:  323-456

              Description.......:  Widget 323 in green

              Part Number.......:  323-555

              Description.......:  Widget 323 in blue

              Part Number.......:  323-777

              Description.......:  Widget 323 in orange

              Quantity..........:  15

              Instructions......:  Pack separately

              Location..........:  East warehouse

               

              Part Number.......:  523-456

              Description.......:  Widget 523 in green

              Quantity..........:  1

              Instructions......:  Ship separately