Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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.