Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Contributor II

## Qlik Sense

I have the tables as below

Call

 Id Users 1 A;B;C;D;E;F;G;H;I 2 A;C;E;I 3 B;C;E;F 4 D;F;G;I 5 A;E;G;H;I 6 B;I;G 7 C;D;F 8 A;E;B 9 D;H;A 10 H;C;G

UserGroup

 User Group A GA B GB C GC D GC E GB F GD G GA H GA I GE

Restricted Groups

 Group1 Group2 GA GD;GE GB GC;GE GC GB;GE

Call Table has the details of the users who have been on a conference call

For Eg in Call 1 - there are 9 users in the conference call and in Call 2- 4 users in a group call etc

And each uses belongs to different groups as per table UserGroup

Third table is a restricted table which means Users from Group GA should not talk to Users in Group GD and GE

Users from group GB should not talk to users in GC and GE and so on.

So as per the above the vice versa also holds good, which means users from group GE should not talk to GA and GB and GC.

Now from the first table we have to find if the bypassed the rule and there was interaction with restricted groups and should show the additional column ByPassed = Y or N

For Eg callid 7, call id10 - Bypassed = 'N' , call id 6 - ByPassed ='Y' because GB should not talk to GE.

Can someone help with the query for this?

Labels (10)

• ### Visualization

1 Solution

Accepted Solutions
Specialist

Hi, interesting exercise you have here, but maybe this could help to solve this:

``````Call:
Id, 	Users
1, 	A;B;C;D;E;F;G;H;I
2, 	A;C;E;I
3, 	B;C;E;F
4, 	D;F;G;I
5, 	A;E;G;H;I
6,	B;I;G
7, 	C;D;F
8, 	A;E;B
9, 	D;H;A
10,	H;C;G];

UserGroup:
User, 	Group
A, 	GA
B, 	GB
C, 	GC
D, 	GC
E, 	GB
F, 	GD
G, 	GA
H, 	GA
I, 	GE];

Restricted_Groups:
Group1,	Group2
GA, 	GD;GE
GB, 	GC;GE
GC, 	GB;GE];

//separating Users and Group2
NoConcatenate
Call_separated:
Id,
SubField(Users, ';') as Users_sep
Resident Call;

NoConcatenate
Restricted_Groups_separated:
Group1,
SubField(Group2, ';') as Group2_sep
Resident Restricted_Groups;

// joining group to user
Left Join (Call_separated)
User as Users_sep,
Group
Resident UserGroup;

Drop Tables Restricted_Groups, UserGroup;

// concatenating everything back to 1 row per Id, but now by group
Call_back:
Id,
concat(Group, ';') as Group
resident Call_separated
GROUP BY Id;

//joining all possible restricted groups
join (Call_back)
Group1 as GR1,
Group2_sep as GR2
resident Restricted_Groups_separated;

drop tables Call_separated, Restricted_Groups_separated;

//calulating does it bypassed restriction and joining back to original Call table
LEFT JOIN (Call)
Id,
Group,
if( sum(If(WildMatch(Group, '*'&GR1&'*') AND WildMatch(Group, '*'&GR2&'*'), 1, 0)) >0, 'Y', 'N') as ByPassed
resident Call_back
group by Id, Group;

drop table Call_back;``````
2 Replies
Specialist

Hi, interesting exercise you have here, but maybe this could help to solve this:

``````Call:
Id, 	Users
1, 	A;B;C;D;E;F;G;H;I
2, 	A;C;E;I
3, 	B;C;E;F
4, 	D;F;G;I
5, 	A;E;G;H;I
6,	B;I;G
7, 	C;D;F
8, 	A;E;B
9, 	D;H;A
10,	H;C;G];

UserGroup:
User, 	Group
A, 	GA
B, 	GB
C, 	GC
D, 	GC
E, 	GB
F, 	GD
G, 	GA
H, 	GA
I, 	GE];

Restricted_Groups:
Group1,	Group2
GA, 	GD;GE
GB, 	GC;GE
GC, 	GB;GE];

//separating Users and Group2
NoConcatenate
Call_separated:
Id,
SubField(Users, ';') as Users_sep
Resident Call;

NoConcatenate
Restricted_Groups_separated:
Group1,
SubField(Group2, ';') as Group2_sep
Resident Restricted_Groups;

// joining group to user
Left Join (Call_separated)
User as Users_sep,
Group
Resident UserGroup;

Drop Tables Restricted_Groups, UserGroup;

// concatenating everything back to 1 row per Id, but now by group
Call_back:
Id,
concat(Group, ';') as Group
resident Call_separated
GROUP BY Id;

//joining all possible restricted groups
join (Call_back)
Group1 as GR1,
Group2_sep as GR2
resident Restricted_Groups_separated;

drop tables Call_separated, Restricted_Groups_separated;

//calulating does it bypassed restriction and joining back to original Call table
LEFT JOIN (Call)
Id,
Group,
if( sum(If(WildMatch(Group, '*'&GR1&'*') AND WildMatch(Group, '*'&GR2&'*'), 1, 0)) >0, 'Y', 'N') as ByPassed
resident Call_back
group by Id, Group;

drop table Call_back;``````
Contributor II
Author

Wow that works cool.

Now to an extension to that.  In CallD1 there are users from 5 different groups, When I click on the CallID 1 it should create 5 different containers GA,GB,GC,GD,GE and each container should show the users from that group.  It should be dynamic as the number of groups vary for each call. And group may increase in future too.

And If I select GA then it should highlight GA in Green and all the restricted groups GD and GE in red. And non restricted groups GB and GC in yellow. Similarly when I select GB then GB should be Green, GC and GE should turn red, GA and GD should turn yellow.

Is there a way to achieve this?

Tags
Community Browser