Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone.
I have a problem. How I can make an inquiry to a query?
STATUS | CODE |
BD | 001 |
BG | 002 |
NP | 002 |
NP | 003 |
C | 003 |
B | 004 |
C | 005 |
NP | 006 |
B | 006 |
P | 001 |
BG | 007 |
B | 002 |
BD | 008 |
RULE: Get the number of status codes that contain a "B". Provided that the same code does not contain a different status (P, C.NP )
If you have a formula like ; count ( { < Status = {" B * "} >} code ) we obtain 7. However, the result has a different status codes "P , C , NP". They should not be counted .
Example.
001 contains a "BD, P" status
002 contains a status "BG, NP ,B"
004 contains status "B" (Correct Score)
006 contains status "B, NP"
007 contains status "BG" (Correct Score)
008 containing "BD" status (Correct Score)
My result is 4. Based on the rule.
I CHANGE: 002 contains a status "BG, NP ,B" (Correct Score) BY 002 contains a status "BG, NP ,B"
Based on your rules, the answer should be 3 with the given data (002 contains BG and NP, so it shouldn't count).
This formula does the trick:
=sum(aggr(if(count({<STATUS={'B*'}>}total <CODE> CODE)=count(total <CODE> CODE),1,0),CODE))
Example file is also attached.
Based on your rules, the answer should be 3 with the given data (002 contains BG and NP, so it shouldn't count).
This formula does the trick:
=sum(aggr(if(count({<STATUS={'B*'}>}total <CODE> CODE)=count(total <CODE> CODE),1,0),CODE))
Example file is also attached.
sum(aggr(count({$ <CODE=P({$<STATUS={"B*"}>} CODE)>-<CODE=P({$<STATUS=-{"B*"}>} CODE)>} CODE), CODE))
THANKS GUYS.
Both answers are correct .