topic Re: Problem with a subquery to field in QlikView

Problem with a subquery to field

rulohx87 — Thu, 20 Aug 2015 15:33:17 GMT

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"

Re: Problem with a subquery to field

Nicole-Smith — Thu, 20 Aug 2015 15:49:47 GMT

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.

Re: Problem with a subquery to field

maxgro — Thu, 20 Aug 2015 16:24:12 GMT

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

Re: Problem with a subquery to field

rulohx87 — Thu, 27 Aug 2015 02:48:37 GMT

THANKS GUYS.

Both answers are correct .