Hello all. I am trying to create a defined field using the If, Then, Else statement below. I am trying to split the rows into groups based on the duration.

I keep getting an (FOC260) AN OPERATION IS MISSING AN ARGUMENT error when checking the statement.

The field DURATION that the statement refers to is also a defined field that is I5 and is a Date Difference function, calculating number of days from Start_Date to End_Date.

IF DURATION GE 0 AND LE 90 THEN '0 to 3 Months'
ELSE IF DURATION GE 91 AND LE 180 THEN '4 to 6 Months'
ELSE IF DURATION GE 181 AND LE 360 THEN '7 to 12 Months'
ELSE IF DURATION GE 361 AND LE 540 THEN '13 to 18 Months'
ELSE IF DURATION GE 541 AND LE 720 THEN '19 to 24 Months'
ELSE IF DURATION GE 721 THEN 'Over Two Years'
ELSE 'Conversion'


Can anyone find a the problem with my statement, or is it possibly because I'm using a defined field?

Thanks in advance.

This message has been edited. Last edited by: MO Admin,

Also, if I enclose the GE/LE part of each line, I get a different error:

(FOC263) EXTERNAL FUNCTION OR LOAD MODULE NOT FOUND: DURATION

IF DURATION GE   0 AND DURATION LT  91 THEN '0 to 3 Months'   ELSE 
IF DURATION GE  91 AND DURATION LT 181 THEN '4 to 6 Months'   ELSE 
IF DURATION GE 181 AND DURATION LT 361 THEN '7 to 12 Months'  ELSE 
IF DURATION GE 361 AND DURATION LT 541 THEN '13 to 18 Months' ELSE 
IF DURATION GE 541 AND DURATION LT 721 THEN '19 to 24 Months' ELSE 
IF DURATION GE 721 THEN 'Over Two Years'                      ELSE
'Conversion';

is how I'd code it.

Thanks Francis, that worked like a charm.

Is there any way to change the sort order of it though? It is my first field and is a BY, but when run, it sorts by 0-3, then 13-18, 19-24, 4-6, etc.

I would define a sort column that would be used in a BY column-name NOPRINT statement:

DURATION_SORT/A2 = 
IF DURATION GE   0 AND DURATION LT  91 THEN '01' ELSE 
IF DURATION GE  91 AND DURATION LT 181 THEN '02' ELSE 
IF DURATION GE 181 AND DURATION LT 361 THEN '03' ELSE 
IF DURATION GE 361 AND DURATION LT 541 THEN '04' ELSE 
IF DURATION GE 541 AND DURATION LT 721 THEN '05' ELSE 
IF DURATION GE 721 THEN '06'                     ELSE
'09';

Don't forget that defines like these probably do not get translated to SQL if you're accessing a DBMS table...

Fantastic. You've been a great help. Thanks a bunch!

You could indent the first 3 values:

Description/A15=
 IF DURATION GE   0 AND DURATION LT  91 THEN ' 0 to 3 Months'   ELSE 
 IF DURATION GE  91 AND DURATION LT 181 THEN ' 4 to 6 Months'   ELSE 
 IF DURATION GE 181 AND DURATION LT 361 THEN ' 7 to 12 Months'  ELSE 
 IF DURATION GE 361 AND DURATION LT 541 THEN '13 to 18 Months'  ELSE 
 IF DURATION GE 541 AND DURATION LT 721 THEN '19 to 24 Months'  ELSE 
 IF DURATION GE 721                     THEN 'Over Two Years'   ELSE
                                             'Conversion';