Question on set analysis building a string in modifier

[gerrycastellino]

Assume I have this table:

name	manager	direct report count	Dept	Cost Center
Jack	Org(jacklyn)	1
ryan	Org(jacklyn)	1
jill	Org(jacklyn)	1
john	Org(jacklyn)	1
jason	Org(jacklyn)	1
mario	Org (Jack)	1
peter	Org (Jack)	1
louis	Org (Jack)	1
mary	Org (Jack)	1

in my tabular report when I display a name(dimension), I need to reach back into my table to get the count of direct reports (expression)

I'm trying to build a set analysis, that will give me this result for "Jack":

Name        Direct Reports

Jack          4

How do I build a set analysis that will look into the manager name for a MATCH of name from my dimension column, something like below, but this is not working.

=count(  {1<  Manager =  {"Org(" + Name + ") } >}  [direct report count])

Gerry.

[MK_QSL]

What is your dimension?

What type of Qlik Object you are using?

[olivierrobin]

hello

maybe using the P modifier, something like P(Name)

and using & instead of + as tou're dealing with strings

[gerrycastellino]

Dimension is Name,

object is straight table.

Gerry.

[techvarun]

Num:
LOAD * INLINE [
    name, manager, DR
    Jack, Org(jacklyn),1
    ryan, Org(jacklyn),1
    jill, Org(jacklyn),1
    john, Org(jacklyn),1
    jason, Org(jacklyn),1
    mario, Org (Jack),1
    peter, Org (Jack),1
    louis, Org (Jack),1
    mary, Org (Jack), 1
];


NoConcatenate
Count:
LOAD
  TextBetween(manager,'(',')') as EmployeeID,
  Count(DR) as MngrCount


Resident Num Group by TextBetween(manager,'(',')')




;

Try above script. You will get a resultant table with direct reports count.

HTH

Varun

[gerrycastellino]

yes, it maybe the case, trying this, but not getting the syntax right, I need to build my search string

=count({<Manager = P({<Manager = {'*' & Name & '*'}>}) >} directreports )

[gerrycastellino]

looks promising !