topic Re: List data only if possible values are true in QlikView

List data only if possible values are true

afsarshaikh — Fri, 27 Jan 2017 09:57:33 GMT

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

Re: List data only if possible values are true

swuehl — Fri, 27 Jan 2017 10:10:20 GMT

=Concat( Aggr(If(Only([Project Assigned])='Y',Manager), Manager),', ')

e.g. in a text box

Re: List data only if possible values are true

tresB — Fri, 27 Jan 2017 10:18:14 GMT

Try this in a text box:

=Concat(DISTINCT{<Manager={"=Count(Distinct Employee)=Count(Distinct {<[Project Assigned]={'Y'}>} Employee)"}>}Manager, ',')

Re: List data only if possible values are true

afsarshaikh — Fri, 27 Jan 2017 10:18:45 GMT

Thanks for your reply Stefan.

However, i'm still getting NULL values.

Re: List data only if possible values are true

afsarshaikh — Fri, 27 Jan 2017 10:27:55 GMT

Thanks for your reply Tresesco.

However, i'm still getting NULL values.

I wan't to display a manager name only if all the values are Y in column 3.

If any of the value is other than Y, no manager to be displayed.

Re: List data only if possible values are true

PrashantSangle — Fri, 27 Jan 2017 10:39:30 GMT

Hi,

create listbox with below expression

Aggr(If(Only([Project Assigned])='Y',Manager), Manager)

Regards,

Re: List data only if possible values are true

tresB — Fri, 27 Jan 2017 10:57:52 GMT

Do you have a sample? In my sample it is working fine. PFA

Re: List data only if possible values are true

Kushal_Chawda — Fri, 27 Jan 2017 11:51:01 GMT

try like below,

create a key in table,

load *,

recno() as Key

FROM table;

Now try below expression

=Concat(DISTINCT{<Key={"=Count(Distinct {<[Project Assigned]={'Y'}>} Employee)=Count(Distinct {<[Project Assigned]={'Y'}>} Manager)"}>}Manager, ',')

Re: List data only if possible values are true

afsarshaikh — Fri, 27 Jan 2017 12:36:35 GMT

I don't have sample data but, I've <space> as a value in my 3rd column. So, it's considering that too...

Re: List data only if possible values are true

tresB — Fri, 27 Jan 2017 12:42:05 GMT

No, that should not be the reason, only 'Y' values is being considered.

Re: List data only if possible values are true

MarcoWedel — Mon, 30 Jan 2017 00:58:39 GMT

Hi,

one solution might be also:

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

hope this helps

regards

Marco

Re: List data only if possible values are true

rahulpawarb — Mon, 30 Jan 2017 07:56:47 GMT

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