Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
HI,
It will work without any issue..
Might be, you missed out group by.
Wouldn´t a Distinct ID work? =count({<Status -= {'Pending'}>} distinct ID)
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)
Thanks for your response. Distinct will not work here
Thanks for your response
Concat(DISTINCT Status, ',') at the back end will not work. it will through an error
Thanks,
Satya
I guess the below expression should work for your case.
=Count(DISTINCT {<ID=e({<Status={'Pending'}>})>} ID)
Hi try this:
Count({<ID=e({<Status={'Pending'}>})>}distinct ID)
HI,
It will work without any issue..
Might be, you missed out group by.