Split data based on Account Ids separated by ";"

[garyk22]

Hi All,

Please see the below data which I am trying to split based on User-Ids. Not sure how to achieve this as I just started using Qlik Sense.

This is the table that I am able to pull directly from Database where I need to split customers based on User Id and call data

account Id	Customer Name	User Ids Assigned	Called By UserId	Number of Calls	Comments
873864736RTW39948I	Jake Bailey	;1911-101103;1911-101106;	1911-101103	1	1 call by 2 users so needs a split with 0 and 1
983729846wqienndxas	Ken Sorjati	;1911-101210;	-	-	Needs no slipt -  1 User 0 calls
0011a00000b28jHAAQ	Cheryl Blackwell	;1911-101108;	1911-101108	1	Needs no slipt -  1 User 1 call
193742091874nnjdchq	Erin West	;1911-101210;1911-101211;	-	-	Needs to be split by muliple users
whfkwhkfdj1y262521	Randy Wayne	;1911-101109;1911-101102;1911-101110;1911-101111;	1911-101109	1	there are 2 calls by 2 users but total users are 4 this needs to be split in 4 users
1911-101110	1

This is the desired output

account Id	Customer Name	User Ids Assigned	Called By UserId	Number of Calls
873864736RTW39948I	Jake Bailey	1911-101103	1911-101103	1
873864736RTW39948I	Jake Bailey	1911-101106		0
983729846wqienndxas	Ken Sorjati	1911-101210	-	0
0011a00000b28jHAAQ	Cheryl Blackwell	1911-101108	1911-101108	1
193742091874nnjdchq	Erin West	1911-101210	-	0
193742091874nnjdchq	Erin West	1911-101211	-	0
whfkwhkfdj1y262521	Randy Wayne	1911-101109	1911-101109	1
whfkwhkfdj1y262521	Randy Wayne	1911-101110	1911-101110	1
whfkwhkfdj1y262521	Randy Wayne	1911-101111	-	0
whfkwhkfdj1y262521	Randy Wayne	1911-101102	-	0

Not sure if this can be achieved, if yes, then how?

Thank you.

[GaryGiles · Accepted solution]

Try adding this to your load script:

subfield([User Ids Assigned],';') as [User Ids Assigned]

If you end up with a row with a blank User Ids Assigned, you may need to avoid the first ';', 

subfield(mid([User Ids Assigned],2),';') as [User Ids Assigned]

[garyk22]

@GaryGiles 

Thank you for your response. I tried this but it shows me only 1 row but I need 4 rows with same name and id but different user ids.

If you see attached picture, Randy Wayne and his account id should show 4 times and if someone has only 2 then 2 times

[GaryGiles]

It should work.  Below is the load script that I just ran and it gave the desired number of rows.

Load
[account Id],
[Customer Name],
subfield(mid([User Ids Assigned],2,len([User Ids Assigned])-2),';') as [User Ids Assigned],
[Called By UserId],
[Number of Calls],
[Comments]
inline [
account Id,Customer Name,User Ids Assigned,Called By UserId,Number of Calls, Comments
873864736RTW39948I, Jake Bailey, ;1911-101103;1911-101106;, 1911-101103, 1, 1, call by 2 users so needs a split with 0 and 1
983729846wqienndxas, Ken Sorjati, ;1911-101210;, -, -, Needs no slipt - 1, User 0 calls
0011a00000b28jHAAQ, Cheryl Blackwell, ;1911-101108;, 1911-101108, 1, Needs no slipt - 1 User 1 call
193742091874nnjdchq, Erin West, ;1911-101210;1911-101211;, -, -, Needs to be split by muliple users
whfkwhkfdj1y262521, Randy Wayne, ;1911-101109;1911-101102;1911-101110;1911-101111;, 1911-101109, 1, there are 2 calls by 2 users but total users are 4 this needs to be split in 4 users
];