Hi,

I currently have a report where I am doing a COUNT of procedure volumes.

So I have a field called Intervention1, and another field called Intervention2. These two fields are simply the name of the intervention (the possible values in both fields are the same).

My report has no problem counting the number of times "A" comes up in Intervention 1 and Intervention 2. The problem I have is, Intervention 1 is the more "important" field, so if there is a value in Intervention1, I don't need to count the value in Intervention2. Basically, I only want to COUNT intervention 2 if Intervention 1 is null. Right now, I just have a WHERE clause (where intervention1 = "A" OR intervention2 = "A").

Is there anyway I can code this?

This message has been edited. Last edited by: hfung1,

sure
several ways
define a field that is exactly what you want
and then count that
DEFINE FILE ...
inter2/A1 MISSING ON = IF INTERVENTION1 EQ 'A' THEN MISSING ELSE INTERVENTION2;
END

I think what you want is:

  
DEFINE FILE ...
INTER2/{same format as intervantion2] MISSING ON = IF INTERVENTION1 EQ MISSING THEN INTERVENTION2 ELSE MISSING;
...
END

Hmm...it's not working for me:

DEFINE FILE
MONTHNO1/A11=EDIT(FISCAL_MONTH_OF_YEAR);
MONTHNO2/A2=EDIT(MONTHNO1,'$$$$$$$$$99');
MONTHNAME/A55=MONTHNO2 | ' ' | FISCAL_MONTH_OF_YEAR_NAME;
V_AGREEMENT.INT2_PROCEDURE/A10V MISSING ON = IF V_AGREEMENT.INT1_PROCEDURE EQ MISSING THEN V_AGREEMENT.INT2_PROCEDURE ELSE MISSING;
END

TABLE FILE V_AGREEMENT
COUNT
V_AGREEMENT.ACCOUNT_NUMBER
BY LOWEST V_AGREEMENT.INT1_PROCEDURE AS 'Intervention1'
BY LOWEST V_AGREEMENT.INT2_PROCEDURE AS 'Intervention2'
WHERE J0.DIM_DATE.DATE GE '2013-04-01';
WHERE J0.DIM_DATE.DATE LE '2014-03-31';
WHERE V_AGREEMENT.INT1_PROCEDURE EQ 'A' OR V_AGREEMENT.INT2_PROCEDURE EQ 'A';

Hi Hugo,

Can you please clarify what you mean by "not working"? Do you get the incorrect value or an error message appears?

Thank you for clarifying.

Cheers,
Tamra Colangelo
Information Builders Customer Support Services.

What do you want to do if intervaention1 is not equal to 'A' but intervention2 is equal to 'A'?

quote:

V_AGREEMENT.INT2_PROCEDURE/A10V MISSING ON = IF V_AGREEMENT.INT1_PROCEDURE EQ MISSING THEN V_AGREEMENT.INT2_PROCEDURE ELSE MISSING

Sorry, what I meant by not working was that the table still shows everything...it does not only display intervention 2 if intervention 1 is null.

quote:

V_AGREEMENT.INT2_PROCEDURE/A10V MISSING ON = IF V_AGREEMENT.INT1_PROCEDURE EQ MISSING THEN V_AGREEMENT.INT2_PROCEDURE ELSE MISSING

Do you need both BY fields?
How would the final output look like?

May be try this,
WHERE (V_AGREEMENT.INT1_PROCEDURE EQ 'A')
OR (V_AGREEMENT.INT1_PROCEDURE EQ MISSING AND V_AGREEMENT.INT2_PROCEDURE NE MISSING);

I would DEFINE:

A_COUNT/I9C= (INT1_PROCEDURE EQ 'A') OR ((INT1_PROCEDURE IS MISSING) AND (INT2_PROCEDURE EQ 'A'));

and simply

SUM A_COUNT

My question is what do you want to happen if INT_1 does not equal to 1 and INT_1 is not missing? Do you still want to look at INT_2?

quote:

Originally posted by RSquared:
My question is what do you want to happen if INT_1 does not equal to 1 and INT_1 is not missing? Do you still want to look at INT_2?

This is what we want to show:

If INT1 = A, only show INT1
If INT1 = BLANK/Missing then go to INT2 and show INT2 = A
if INT 1 = NOT A but also NOT MISSING/BLANK, so let's say INT1 = B, it should NOT show either INT1 or INT 2

So basically, only look at INT2 if INT1 is missing/blank

quote:

I would DEFINE:

A_COUNT/I9C= (INT1_PROCEDURE EQ 'A') OR ((INT1_PROCEDURE IS MISSING) AND (INT2_PROCEDURE EQ 'A'));

and simply

SUM A_COUNT

As suggested above following can be used:
DEFINE FILE XYZ
A_COUNT/I9C= IF (INT1_PROCEDURE EQ 'A') OR ((INT1_PROCEDURE IS MISSING) AND (INT2_PROCEDURE EQ 'A')) THEN 1 ELSE 0;
END
TABLE FILE XYZ
PRINT A_COUNT
WHERE A_COUNT NE 0
END

This should return only the required set of records.