Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

Separating several values in one field

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

1 Solution

Accepted Solutions
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


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
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


talk is cheap, supply exceeds demand
datanibbler
Champion
Champion
Author

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

datanibbler
Champion
Champion
Author


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.