topic Question on set analysis building a string in modifier in QlikView

Question on set analysis building a string in modifier

gerrycastellino — Fri, 20 Apr 2018 06:59:45 GMT

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.

Re: Question on set analysis building a string in modifier

MK_QSL — Fri, 20 Apr 2018 07:17:04 GMT

What is your dimension?

What type of Qlik Object you are using?

Re: Question on set analysis building a string in modifier

olivierrobin — Fri, 20 Apr 2018 07:17:22 GMT

hello

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

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

Re: Question on set analysis building a string in modifier

gerrycastellino — Fri, 20 Apr 2018 07:21:02 GMT

Dimension is Name,

object is straight table.

Gerry.

Re: Question on set analysis building a string in modifier

techvarun — Fri, 20 Apr 2018 07:21:49 GMT

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

Re: Question on set analysis building a string in modifier

gerrycastellino — Fri, 20 Apr 2018 07:23:01 GMT

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 )

Re: Question on set analysis building a string in modifier

gerrycastellino — Fri, 20 Apr 2018 07:29:13 GMT

looks promising !