Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
gerrycastellino
Creator III
Creator III

Question on set analysis building a string in modifier

Assume I have this table:

     

     

namemanagerdirect report countDeptCost Center
JackOrg(jacklyn)1
ryanOrg(jacklyn)1
jillOrg(jacklyn)1
johnOrg(jacklyn)1
jasonOrg(jacklyn)1
marioOrg (Jack)1
peterOrg (Jack)1
louisOrg (Jack)1
maryOrg (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.

6 Replies
MK_QSL
MVP
MVP

What is your dimension?

What type of Qlik Object you are using?

olivierrobin
Specialist III
Specialist III

hello

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

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

gerrycastellino
Creator III
Creator III
Author

Dimension is Name,

object is straight table.

Gerry.

techvarun
Specialist II
Specialist II

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

looks promising !