topic Split string field into table rows in QlikView

Split string field into table rows

NemoAndStitch22 — Sat, 01 May 2021 13:03:16 GMT

Hiya,

I have a data set that has about 40 columns and one of those has 'Enquiry Numbers' which can hold 1-x amount of numbers, which I would like to split out, for example:

ID | Enquiry Number

123 | 15, 25, 68, 52

124 | 2, 98, 34, 76

126 | 18, 24, 88, 92, 48

127 | 5

128 | 92, 16

and so on and so forth, what I would like to is split each Enquiry and have it pull in all the table data for that ID but only one of the Enquiry Numbers.

I've found this post with an answer: Suggested Solution

However, the answer shows that you need to repeat the solution for each part of the field that needs splitting.

I can have any number of Enquiry Numbers in this field, so don't want to have to repeat the process 50 times just to capture them all.

Is there a better way?

Thanks in advance

Re: Split string field into table rows

Digvijay_Singh — Sat, 01 May 2021 13:24:55 GMT

I think the best way to create multiple rows out of one field is subfield function. In the script if we use something like subfield([Enquiry Number],','), It should create multiple row dynamically for each value separate by comma.

Re: Split string field into table rows

NemoAndStitch22 — Sat, 01 May 2021 14:39:23 GMT

Omg, perfect! That has worked perfectly thank you so much!!!