3 Replies Latest reply on Mar 6, 2015 8:36 AM by heckam10

    Need specific position when multiple occurrences

    heckam10

      Title

      Need specific position when multiple occurrences

      Post

      I have a chunk of text in a variable that could contain repeat values. I need to find the position of a specific value.

      This is my list obtained using execute sql.

                8mm Projector    25    1    3
                Digital Projector    3095    26    3
                Overhead Projector    30    2    3
                Projector Screen    105    2    3
                Slide Projector    45    1    3

      I need it to find the 30 in the 3rd row, not the 30 in the 2nd row.

       

        • 1. Re: Need specific position when multiple occurrences
          philmodjunk

          Is it that you want to find the first value "30" anywhere in the field that is not part of a larger string such as "3095"?

          What character did you specify as a field separator?

          Is the value you want to calculate from this example, row 3, Column 2?

           

          • 2. Re: Need specific position when multiple occurrences
            raybaudi

            You'll need to search for " 30 ", something like:

            Position ( " " & YourField & " " ; " 30 " ; 1 ; 1 )

            • 3. Re: Need specific position when multiple occurrences
              heckam10

              I only want to find the value in Row 3, Column 2, in this case, 30. I do NOT want it to also find the 30 that is part of Row 2, Column 2.

              My end goal is to take this chunk of data ($data) and format the values in Column 2 as currency. My current script is:

              Set Variable [$Count; Value:ValueCount ($data)]
              Loop
              Set Variable [$data; Value:Replace ($data;Position ( $data; GetValue (Substitute (GetValue ($data; $Count) ;"    " ; "¶" ); 2); 1; 1);Length (GetValue (Substitute (GetValue ($data; $Count) ;"    " ; "¶" ); 2));Currency ( GetValue (Substitute (GetValue ($data; $Count) ;"    " ; "¶" ); 2); ""; "$"; ""; 2; 1))
              Set Variable [$Count; Value:$Count-1]
              Exit Loop If $Count = 0
              End Loop