topic Splitting a field into multiple values in script in QlikView

Splitting a field into multiple values in script

Fri, 06 Nov 2009 23:12:47 GMT

I have a column in a table containing strings of list of values separated by commas (e.g. "val1, val2, val3").

I would like to import the single values as if in a separate, related table.

So I could select from a field "val1" or "val2".

Is this possible?

Fri, 06 Nov 2009 23:25:32 GMT

use right, left or mid clauses, accord of the position in the column.

left(val1,2)

right(val3,3)

mid(val2,2,3)

Fri, 06 Nov 2009 23:40:08 GMT

Thank you Alfredo!

I'm sorry if I didn't explain well, but I can't. I don't know in advance the number of values, nor the number of them.

I could have a row "aaaa", another "bb, ccccc".

It really should be a 1-N relationship in the source db, but it isn't and I can't change that!

Thanks anyway for your fast response!

If you have any other thoughts, please let me know!

johnw — Fri, 06 Nov 2009 23:57:11 GMT

Do you want one ROW for every value in the list? If so, you can use the subfield() function:

LOAD
"Something"
,"Something Else"
,subfield("My Concatenated Field",',') as "My Field"
FROM...
;

Sat, 07 Nov 2009 00:08:11 GMT

That's it!

Thank you John, you're the best!