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,
Kevin ______________________ Production: WebFocus 7.6.11 on Win2K3 Server Test: WebFocus 7.6.11 on Win2K3 Server Formats: Excel2K, PDF, HTML
June 18, 2009, 02:45 PM
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
Kevin ______________________ Production: WebFocus 7.6.11 on Win2K3 Server Test: WebFocus 7.6.11 on Win2K3 Server Formats: Excel2K, PDF, HTML
June 18, 2009, 02:48 PM
Francis Mariani
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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 18, 2009, 03:11 PM
MO Admin
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.
Kevin ______________________ Production: WebFocus 7.6.11 on Win2K3 Server Test: WebFocus 7.6.11 on Win2K3 Server Formats: Excel2K, PDF, HTML
June 18, 2009, 03:15 PM
Francis Mariani
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...
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 18, 2009, 03:32 PM
MO Admin
Fantastic. You've been a great help. Thanks a bunch!
Kevin ______________________ Production: WebFocus 7.6.11 on Win2K3 Server Test: WebFocus 7.6.11 on Win2K3 Server Formats: Excel2K, PDF, HTML
June 18, 2009, 03:33 PM
j.gross
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';