Split concatenated field into table

[alexandra_dh]

Hi Everybody,

I've searched in many post but not find my answer.

I've a file that contains two columns. 

- GroupName : Name of my AD Group

- Users : Liste of users tha belongs to this group, separated with ';'

It looks like this :

GROUP      MEMEBER

GrpA           USER1;USER25,USER99;USER457;USER6989

GrpB          USER1;USER99;USER77

GRPC         USERGuest

I want to have a final table that looks like this

GROUP        USER

GraA            USER1

GrpA            USER25 

GrpB            USER1

etc .....

Thanks for your help

[alexandra_dh · Accepted solution]

It's OK

I just needed the subfield() Function. (no need to add a parameter with the index number)

Load

GROUP

subfield(MEMBER,';') as Member

[manoranjan_d]

Hi Alex,

Solution is great,

But one  concern i have suppose if the subfiled is combination of ; and ,

how we will segregate to get the output correctly?

example after USER25 we have comma

GROUP,MEMEBER

GrpA, USER1;USER25,USER99;USER457;USER6989

[alexandra_dh]

Hi Manoranjan,

That's right, I've made a short answer.

The true is I made it in 3 steps.

Fisrt step si to load my file in a unique column into a table TMP 

TMP:

LOAD [@1:n] as Lig_All

From [..\my_file.xls]

The second step is to load in another table the two columns using the ',' as a separator

With the FindOneOf function, I get the index of the start of my second column containing the list of users.

With the Mid function, I get the string after this index

TMP2:

Load

subfield(Lig_All,',',1) as GroupName, 

Mid(Lig_All,FindOneOf(Lig_All,',',1)+1) as MemberList .

Resident TMP;

The Last Step is to use the subfield Function to assign each user to his group

FINAL :

Load

GroupName,

Subfield(MemberList,'';') as User

resident TMP2;

I hop it could help other people with the same problem.