Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

datanibbler
Esteemed Contributor

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
MVP & Luminary
MVP & Luminary

Re: Separating several values in one field

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
3 Replies
MVP & Luminary
MVP & Luminary

Re: Separating several values in one field

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
Esteemed Contributor

Re: Separating several values in one field

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
Esteemed Contributor

Re: Separating several values in one field


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.