topic Re: Separating several values in one field in QlikView

Separating several values in one field

datanibbler — Thu, 13 Nov 2014 10:33:31 GMT

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 — Thu, 13 Nov 2014 10:39:49 GMT

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

datanibbler — Thu, 13 Nov 2014 10:44:18 GMT

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

datanibbler — Thu, 13 Nov 2014 14:53:54 GMT

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.