topic Re: Split concatenated field into table in QlikView

Split concatenated field into table

alexandra_dh — Sat, 16 Nov 2024 19:42:40 GMT

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

Re: Split concatenated field into table

alexandra_dh — Fri, 08 Nov 2019 17:29:01 GMT

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

Re: Split concatenated field into table

manoranjan_d — Sat, 09 Nov 2019 15:42:31 GMT

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

Re: Split concatenated field into table

alexandra_dh — Tue, 12 Nov 2019 09:45:42 GMT

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.