Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
afsarshaikh
Creator
Creator

List data only if possible values are true

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 EmployeeProject Assigned
ABCE1Y
ABCE2N
ABCE3NULL
XYZE4Y
XYZE5Y
XYZE6Y

Regards,

Afsar Shaikh

Regards,
Afsar Shaikh
Project Manager- QlikView Dev. & Support
Afsar Shaikh
Email- afsar.shaikh@live.com
11 Replies
MarcoWedel

Hi,

one solution might be also:

QlikCommunity_Thread_247781_Pic1.JPG

QlikCommunity_Thread_247781_Pic2.JPG

=Aggr(Only({1<Manager={"=Max([Project Assigned]='Y')"}>} Manager), Manager)

hope this helps

regards

Marco

rahulpawarb
Specialist III
Specialist III

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