Pick Match expression help

[mahitham]

Hi Experts,

Can any one please help me on below requirement.

I have created a Table like below in Qlik. I have copied what Expressions I have used in Each measure.

But client want to se this table in below format.

I have tried like below

Created Category field in inline

Load * Inline [

Category

Top Employees

Top Manager

];

Next in a Pivot Table Taken Department and Category as fields and write the expression  for Name Column like below,

Name:

Pick(Match(Category,'Top Employees','Top Manager'),

FirstSortedValue( {<Employee={"=len(trim(Employee))>0"}>}Employee,
-Aggr(Count({<Employee={"=len(trim(Employee))>0"},MonthDiff={0}>} Trades),Department, Employee)),

FirstSortedValue( {<Manager={"=len(trim(Manager))>0"}>}Manager,
-Aggr(Count({<Manager={"=len(trim(Manager))>0"},MonthDiff={0}>} Trades),Department, Manager))

))

But getting result like below. Only First expression is working in above pick match expression not getting second expression results. Please help me to fix the issue.

Thanks in advance

[Anil_Babu_Samineni]

did you miss single Quote?

Pick(Match(Category, 'Top Employees', 'Top Manager'),

FirstSortedValue( {<Employee={"=len(trim(Employee))>0"}>}Employee,
-Aggr(Count({<Employee={"=len(trim(Employee))>0"},MonthDiff={0}>} Trades),Department, Employee)),

FirstSortedValue( {<Manager={"=len(trim(Manager))>0"}>}Manager,
-Aggr(Count({<Manager={"=len(trim(Manager))>0"},MonthDiff={0}>} Trades),Department, Manager))

))

[mahitham]

Hi @Anil_Babu_Samineni 

Thanks for your reply. I have applied Single Quotes in app here its missing.Still have the issue.

Is this issue is because of this tow unrelated  First sorted values  expressions usage in pick match function.

Here if I moved second Manger expression to first Then Getting Manger values only one expression is working 2nd expression values not getting.

[Anil_Babu_Samineni]

Does category is associated in the model?

Try this, For a while and help what are you getting?

Pick(Match(Category, 'Top Employees', 'Top Manager'), FirstSortedValue(Employee, -Department), FirstSortedValue( Manager, Department))

Or

Pick(Match(Category, 'Top Employees', 'Top Manager'), 10, 20)

[mahitham]

HI @Anil_Babu_Samineni 

Category is not associated with datamodel. Just created a field in inline script with expression labels.

Please find the below results for 2 expressions.

[Anil_Babu_Samineni]

Perhaps this?

Dimension:
ValueList('Top Employees', 'Top Manager')

Expression:
If(ValueList('Top Employees', 'Top Manager')='Top Employees',
FirstSortedValue( {<Employee={"=len(trim(Employee))>0"}>}Employee,
-Aggr(Count({<Employee={"=len(trim(Employee))>0"},MonthDiff={0}>} Trades),Department, Employee)),
If(ValueList('Top Employees', 'Top Manager')='Top Manager',
FirstSortedValue( {<Manager={"=len(trim(Manager))>0"}>}Manager,
-Aggr(Count({<Manager={"=len(trim(Manager))>0"},MonthDiff={0}>} Trades),Department, Manager))))