How to avoid specific string values in set analysis

[SatyaPaleti]

Hi Experts,

I am having following requirement.

We need to count no of id's based on below condition.

We had a field called STATUS and STATUS has a values like ('Pending', 'Completed', 'On Going', 'Approved', 'on Hold', 'Processing Started').

We need to avoid count of ID's were STATUS is Pending. 

for this we can write a simple set analysis  =count({<Status -= {'Pending'}>}ID)

but here condition is. Every ID may have 2 or more STATUS.

ID	Status
1	Pending
1	Completed
1	On Going
2	Completed
2	On Going
2	Approved
3	On Going
3	Approved
4	Pending
4	On Going

If we look in to above table ID's 1 & 4 has STATUS 'Pending' so we have to ignore both 1 and 4. so the count of ID's should be only 2  because 2 & 3 id's doesn't have STATUS 'Pending' we have to consider only these records. 

How can we achieve this. Please help on this

Thanks,

Satya 

[Diego_780]

Wouldn´t a Distinct ID work? =count({<Status -= {'Pending'}>} distinct ID)

[MayilVahanan]

Hi

Try like below

Sum(Aggr(If(not WildMatch(Concat(DISTINCT Status, ','), '*Pending*'), 1), ID))

or, if you want to use in back end, try like below

Temp2:
LOAD * INLINE [
ID, Status
1, Pending
1, Completed
1, On Going
2, Completed
2, On Going
2, Approved
3, On Going
3, Approved
4, Pending
4, On Going
];

Load ID, Concat(DISTINCT Status, ',') as CombinedStatus Resident Temp2 Group by ID;

Exp:

=Count({<CombinedStatus -= {"*Pending*"}>} Distinct ID)

[SatyaPaleti]

Thanks for your response. Distinct will not work here

[SatyaPaleti]

Hi @MayilVahanan 

Thanks for your response

Concat(DISTINCT Status, ',')  at the back end will not work. it will through an error

Thanks,

Satya

[tresB]

I guess the below expression should work for your case.

=Count(DISTINCT {<ID=e({<Status={'Pending'}>})>} ID)

[Gabbar]

Hi try this:
Count({<ID=e({<Status={'Pending'}>})>}distinct ID)

[MayilVahanan · Accepted solution]

HI,

It will work without any issue.. 

Might be, you missed out group by.