Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alexandra_dh
Contributor III
Contributor III

Split concatenated field into table

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

 

 

Labels (2)
1 Solution

Accepted Solutions
alexandra_dh
Contributor III
Contributor III
Author

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

 

View solution in original post

3 Replies
alexandra_dh
Contributor III
Contributor III
Author

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
Specialist
Specialist

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
Contributor III
Contributor III
Author

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.