List data only if possible values are true

[afsarshaikh]

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

[swuehl]

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

e.g. in a text box

[tresesco · Accepted solution]

Try this in a text box:

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

[afsarshaikh]

Thanks for your reply Stefan.

However, i'm still getting NULL values.

[afsarshaikh]

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.

[PrashantSangle]

Hi,

create listbox with below expression

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

Regards,

[tresesco]

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

[Kushal_Chawda]

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, ',')

[afsarshaikh]

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

[tresesco]

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