Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I stucked in some logic and i need a help from you.
I'm sharing an example below, which has 3 columns as Manager, Employee and Project Assigned.
I wan't to list only those manger whose all employees have project assigned.
So, in below example only Manger XYZ should be shown since, under him everyone's project assigned. Also, Manager 'ABC' shouldn't be displayed since, it has N and NULL values.
Could anyone help me to achieve this? Thanks
Manager | Employee | Project Assigned |
ABC | E1 | Y |
ABC | E2 | N |
ABC | E3 | NULL |
XYZ | E4 | Y |
XYZ | E5 | Y |
XYZ | E6 | Y |
Regards,
Afsar Shaikh
Hi,
one solution might be also:
=Aggr(Only({1<Manager={"=Max([Project Assigned]='Y')"}>} Manager), Manager)
hope this helps
regards
Marco
Hello Afsar,
Hope you are doing well!
ProjectData:
Load * Inline [
Manager, Employee, "Project Assigned"
ABC, E1, Y
ABC, E2, N
ABC, E3,
XYZ, E4, Y
XYZ, E5, Y
XYZ, E6, Y
];
MgrData:
LOAD Manager,
OnProject,
Total
WHERE OnProject = Total;
LOAD Manager,
SUM(If([Project Assigned] = 'Y',1,0)) AS OnProject,
COUNT([Project Assigned]) AS Total
Resident ProjectData
GROUP BY Manager;
In this script, MgrData table will hold data of Managers whose all reporting employees are on project.
Regards!
Rahul