3 Replies Latest reply: Nov 13, 2014 10:12 AM by Friedrich Hofmann RSS

    Separating several values in one field

    Friedrich Hofmann

      Hi,

       

      I have an Excel_file to load where the employees populating the list now write down so-called track_numbers in one field. However, since they check several tracks in one go (several boxes, that is), they write all the numbers in one line (in one field), separated by a

      comma and a BLANK.

      The next step will be to fetch from our database the info on who was the last one to post any transaction on that tracking_number(s),

      so I need something I can use in a WHERE EXISTS() clause.

       

      => I suppose that to that end I have to separate all the values in that field.

          I have done that once before in another app, but the issue is that I don't know beforehand the number of values that are stored in
          that field - there may be just 5 or there may be 20 or whatever.

       

      => Is there any way I can parse the list line by line and then make that comma-separated list into a table with as many records, but just one field?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: Separating several values in one field
          Gysbert Wassenaar

          That's what the subfield function is for. If you use it without the third parameter it will create a record for each item in the list separated by the string passed as second parameter: subfield(MyField, ', ') as MyField_Item

            • Re: Separating several values in one field
              Friedrich Hofmann

              Hi Gysbert,

               

              that is cool. I knew that function, I have used it the last time I did this, but I didn't know it could be used without that parameter. Cool.

              So I "just" have to parse the list line by line and use that function on that field where I have that comma-separated list of values?

              Thanks a lot!

               

              Best regards,

               

              DataNibbler

                • Re: Separating several values in one field
                  Friedrich Hofmann


                  Hi Gysbert,

                   

                  now I have the following follow-up problem:

                  - In the subroutine, I create 2 fields in the first step

                    => Then I derive from that an auxiliary table with just a line_ID and the nr. of records

                    => I join that info to the first table I created and

                    => delete the auxiliary table again.

                   

                  For some reason, when I run the script (the subroutine is called 4 times), I get a fragmented file, the filename is thus different and the whole thing doesn't work.

                   

                  Now I made a change and left out the whole joining.

                  => I just create two tables in the subroutine

                     - one which is just appended to every time the subroutine is called

                     - one which has just 4 lines (when the subroutine is called 4 times)

                  <=> The issue now is, in every iteration of the subroutine I want to extract just 1 record into that second table

                          <=> but I cannot use the FIRST 1 parameter as the first table is appended to, thus I would need to extract the

                                 first record from those which I added in the 2nd to 4th iteration, not the first one (which is always the same)

                   

                  Do you have an idea? I would need the nr_of_lines from the last iteration and so construct the line that I have to extract.

                   

                  P.S.: OK, I've made it. That part works. I'll open a new thread for any new problems that might arise from here.