Qlik Sense

[govardhana_r]

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?

[justISO · Accepted solution]

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

Call:
load * inline [
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:
load * inline [
User, 	Group
A, 	GA
B, 	GB
C, 	GC
D, 	GC
E, 	GB
F, 	GD
G, 	GA
H, 	GA
I, 	GE];

Restricted_Groups:
load * inline [
Group1,	Group2
GA, 	GD;GE
GB, 	GC;GE
GC, 	GB;GE];

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

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

// joining group to user
Left Join (Call_separated)
Load
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:
Load
Id,
concat(Group, ';') as Group
resident Call_separated
GROUP BY Id;

//joining all possible restricted groups
join (Call_back)
load
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)
load
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;

[govardhana_r]

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?