Focal Point
[CLOSED] IF ELSE Problem

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/294107744

May 21, 2010, 02:25 PM
BJosipovic
[CLOSED] IF ELSE Problem
I am having trouble getting this IF ELSE statement to work. For some reason it does not get to the last IF that should return a 5, but when I have that IF statemetn seperate it works fine.

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 (CURRENT_ACTUAL_TP3 - CURRENT_PLAN_TP3)* -1 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;

THIS ONE WORKS
MROS_PLAN	 = 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;

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


WebFOCUS 7.6.9
Windows
all output (Excel, PDF, HTML)
May 21, 2010, 03:09 PM
Prarie
Try throwing an Else 0 at the end of all that.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
Prarie'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 ? ;



WebFOCUS 7.7.05
Please 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).




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
quote:
(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.


WebFOCUS 8.1.05
Windows
http://ibiwebfocus.wordpress.com
https://www.facebook.com/groups/ibi.webfocus/
You 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 )


WebFOCUS 7.6.4
Windows XP against an Oracle Database mainly SCT Banner Higher Education ODS and EDW
Various output formats


Guy Brenckle
Budget Analyst
University of Northern Colorado