May 21, 2010, 04:51 PM
Dan SatchellPrarie's suggestion of adding a final ELSE is very valid. IF..THEN..ELSE logic should always have a final ELSE in case none of the IF criteria are satisfied. Also, you could try adding some parathensis and switching around your calculation with -1.
MROS_PLAN = IF (((CURRENT_ACTUAL_TP3 - CURRENT_PLAN_TP3)/(CURRENT_ACTUAL_TP8A - CURRENT_PLAN_TP8A)) GT 0 AND
(CURRENT_ACTUAL_TP3 - CURRENT_PLAN_TP3) LT 0)
THEN (-1 * (CURRENT_ACTUAL_TP3 - CURRENT_PLAN_TP3))
ELSE IF (((CURRENT_ACTUAL_TP3 - CURRENT_PLAN_TP3)/(CURRENT_ACTUAL_TP8A - CURRENT_PLAN_TP8A)) GT 0 AND
(CURRENT_ACTUAL_TP3 - CURRENT_PLAN_TP3) GT 0)
THEN (CURRENT_ACTUAL_TP3 - CURRENT_PLAN_TP3)
ELSE IF (((CURRENT_ACTUAL_TP3 - CURRENT_PLAN_TP3)/(CURRENT_ACTUAL_TP8A - CURRENT_PLAN_TP8A)) LT 0 AND
(CURRENT_ACTUAL_TP3 - CURRENT_PLAN_TP3) LT 0)
THEN 5
ELSE ? ;
May 21, 2010, 08:12 PM
DougPlease consider the following code where I created your CURRENT* fields (total bogus I might add) then simplified your IF...Then...ELSE. And, OBTW, I did add the ELSE at the end which really is not "required" other then as a "best practice technique".
DEFINE FILE CAR
CURRENT_ACTUAL_TP3 = MPG + HEIGHT;
CURRENT_PLAN_TP3 = HEIGHT ;
CURRENT_ACTUAL_TP8A = WIDTH ;
CURRENT_PLAN_TP8A = WIDTH * .95 ;
CALC1/D12.2 = CURRENT_ACTUAL_TP3 - CURRENT_PLAN_TP3 ;
CALC2/D12.2 = CURRENT_ACTUAL_TP8A - CURRENT_PLAN_TP8A ;
MROS_PLAN = IF ((CALC1)/(CALC2)) GT 0 AND (CALC1) LT 0 THEN -CALC1
ELSE IF ((CALC1)/(CALC2)) GT 0 AND (CALC1) GT 0 THEN CALC1
ELSE IF ((CALC1)/(CALC2)) LT 0 AND (CALC1) LT 0 THEN 5
ELSE 0;
END
TABLE FILE CAR
PRINT CALC1 CALC2 MROS_PLAN
BY MODEL
END
It seems that the source of the error is "
(CURRENT_ACTUAL_TP3 - CURRENT_PLAN_TP3)* -1" more then anything else.
I would like you to, also, consider a more structured approach to your coding which would make it easier to analyze and maintain for those who follow (Who knows, It may be one of us).
May 22, 2010, 04:01 AM
Ram Prasad Equote:
(CURRENT_ACTUAL_TP3 - CURRENT_PLAN_TP3)* -1
This will give you error.
Without ELSE phrase, when none of the conditions were true, then WebFOCUS will take the previous true conditions value.
TABLE FILE CAR
SUM
DEALER_COST
BY COUNTRY
BY CAR
ON TABLE HOLD AS HLD FORMAT ALPHA
END
DEFINE FILE HLD
CC_DEF/A20=IF COUNTRY EQ 'ENGLAND' THEN 'value1' ELSE IF COUNTRY EQ 'FRANCE' THEN 'value2';
END
TABLE FILE HLD
PRINT
CAR
CC_DEF
COMPUTE CC_COMP/A20=IF COUNTRY EQ 'ENGLAND' THEN 'value1' ELSE IF COUNTRY EQ 'FRANCE' THEN 'value2';
BY COUNTRY
END
Follow Dan or Doug method of using IF THEN ELSE.
May 24, 2010, 09:14 AM
GuyYou might want to focus on Dan's comment about the calculation with the -1.
I have had to enclose a -1 within paranthensis to get calculations to work on numerous occasions.
( (-1) * Field )