Help for Set expression to fetch count of possible indirectly selected value ?

[prahlad_infy]

Hello Everyone ,

For given data i need to find the count of employee  for each employee based on shared id .

id	employee	designation	type
1	a	ax	t1
4	a	ax	t5
2	a	bx	t1
5	b	bx	t1
6	b	bx	t2
6	b	bx	t5
7	b	bx	t2
8	b	bx	t2
3	b	cx	t1
1	c	cx	t4
7	c	cx	t3
2	d	dx	t4
7	a	ex
9	f	fx	t2
3	e	gx	t6
4	g	gx	t1
5	g	gx	t5

Result of set expression 

employee	id#	various_id	count of employee id sharing various_id	various_id shared by
a	4	1 , 2 , 4 , 7	5	a,b,c,d,g
b	5	3 , 5 , 6 , 7 , 8	4	a,b,c,e
c	2	1 , 7	4	a,b,c
d	1	2	2	a,d
e	1	3	?	?
f	1	9	?	?
g	2	4 , 5	?	?

[prahlad_infy]

@MarcoWedel 
Thank You

[MarcoWedel · Accepted solution]

Hi,

as you already thanked me, I kind of feel obliged to post an answer  😉

So one solution (although not using set expressions) might be:

 

 

table1:
LOAD id, 
     employee, 
     designation, 
     type
FROM [https://community.qlik.com/t5/QlikView-App-Dev/Help-for-Set-expression-to-fetch-count-of-possible-indirectly/m-p/1852380] (html, codepage is 1252, embedded labels, table is @1);

table2:
LOAD id,
     employee as employeeSharingIds
Resident table1;

hope this helps

Marco

[MarcoWedel]

@prahlad_infy , if your question is answered, please close your thread by accepting a solution.

thanks

Marco

[prahlad_infy]

Yeah Sure . Sorry for delay .