Problem with a subquery to field

[rulohx87]

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"

[Nicole-Smith · Accepted solution]

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.

[maxgro]

sum(aggr(count({$ <CODE=P({$<STATUS={"B*"}>} CODE)>-<CODE=P({$<STATUS=-{"B*"}>} CODE)>} CODE), CODE))

[rulohx87]

THANKS GUYS.

Both answers are correct .