Separate String and combine into one field

[crystles]

I have a field that we are pulling from Active Directory that they are using to store user roles for a separate program.

I need to use these fields in a document, but the problem is, if the user has more than one role, it just adds them to the field with a space and comma separating them. See example

AD UserName	RoleID
John	Admin
Sally	Admin, Update
Bob	Update

Looking at the example, the user "John" and "Bob" would not be a problem. However, Sally has two roles, and they are both listed in the same field.

What I need my report to look like, in order to feed it into the Qlikview document we have, is for it to look like the example below instead. Any help or advice would be greatly appreciated.

AD UserName	RoleID
John	Admin
Sally	Admin
Sally	Update
Bob	Update

[sunny_talwar · Accepted solution]

Try like this:

LOAD [AD UserName],

           Subfield(RoleID, ', ') as RoleID

FROM ....

[crystles]

That worked perfectly! Thank you!