Focal Point
Determining MAX value within separate date fields

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

November 15, 2007, 10:23 AM
Harry M Cleveland
Determining MAX value within separate date fields
I have 4 date fields. I need to determine the most recent date prior to a 'seed' date and then retrieve the code that corresponds to the date.

For example, I have the following fields:

DATE1 CODE1
DATE2 CODE2
DATE3 CODE3
DATE4 CODE4

Seed date: 20071101

DATE1 20061231 CODE1 AAAAAA
DATE2 20071231 CODE2 BBBBBB
DATE3 blank CODE3 blank
DATE4 20070801 CODE4 DDDDDD

I want the value of CODE4 because the DATE4 (20070801) is the most recent prior date to the seed date (20071101)

Anyone have any ideas how I might code this monstrosity?

Thanks in advance!


FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
November 15, 2007, 10:28 AM
Francis Mariani
This might do it:

TABLE FILE BLAHBLAH
PRINT CODE
BY HIGHEST 1 DATE
WHERE DATE LE '&SEED_DATE'
ON TABLE SAVE
END



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
November 15, 2007, 10:45 AM
Harry M Cleveland
Thank you Francis for the rapid reply. I see what your code is doing; however, there are 4 'separate' date fields in the dataset. That is, the dates are not contained in a single field so the HIGHEST syntax would not work.

Unless I am missing something in your example?


FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
November 15, 2007, 10:58 AM
Francis Mariani
I was misled by your example, you showed multiple rows of data.

To determine the maximum value within multiple columns of a single row, try MAX():

MAX_DATE/YYMD = MAX(DATE1, DATE2, DATE3, DATE4);
MAX_CODE/A10  =
  IF DATE1 EQ MAX_DATE THEN CODE1 ELSE
  IF DATE2 EQ MAX_DATE THEN CODE2 ELSE
  IF DATE3 EQ MAX_DATE THEN CODE3 ELSE CODE4;


Hopefully this helps a little.


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
November 15, 2007, 11:09 AM
mgrackin
Nice one Francis. I did not know about the MAX() function. I have now learned my new trick for the day.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
November 15, 2007, 11:20 AM
TexasStingray
  
-SET SEEDDATE = 20071101;

DEFINE FILE ...
MAXDATE1/MDYY = IF DATE1 LE &SEEDDATE AND DATE1 GT LAST MAXDATE1 THEN DATE1 ELSE LAST MAXDATE1;

MAXDATE2/MDYY = IF DATE2 LE &SEEDDATE AND DATE2 GT LAST MAXDATE2 THEN DATE2 ELSE LAST MAXDATE2;

MAXDATE3/MDYY = IF DATE3 LE &SEEDDATE AND DATE3 GT LAST MAXDATE3 THEN DATE3 ELSE LAST MAXDATE3;

MAXDATE4/MDYY = IF DATE4 LE &SEEDDATE AND DATE4 
GT LAST MAXDATE4 THEN DATE4 ELSE LAST MAXDATE4;

MAXDATE0/MDYY = IF MAXDATE1 GT MAXDATE2 AND MAXDATE1 GT MAXDATE3 AND MAXDATE1 GT MAXDATE4 THEN MAXDATE1 
ELSE IF MAXDATE2 GT MAXDATE1 AND MAXDATE2 GT MAXDATE3 AND MAXDATE2 GT MAXDATE4 THEN MAXDATE2 
ELSE IF MAXDATE3 GT MAXDATE1 AND MAXDATE3 GT MAXDATE2 AND MAXDATE3 GT MAXDATE4 THEN MAXDATE3 
ELSE MAXDATE4; 
END

TABLE FILE ....
PRINT MAXDATE0
BY HIGHEST 1 MAXDATE0
END


Tex
Hope this helps or gives you some idea of what to do.




Scott

November 15, 2007, 11:20 AM
Francis Mariani
Old dogs - new tricks, huh? We can still learn!


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
November 15, 2007, 01:23 PM
Harry M Cleveland
Thanks Francis,

Other than having to change the DATE fields into numerics, it worked like a dream.

I use MAX all of the time; however, I never thought of using it the way you recommended.

I had started coding something similar to TexasStingray's recommendation; however, I did not want to have to explain it to the user or to have to support the code.

I too have learned a new trick.

My thanks to both of you.

Harry


FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
November 15, 2007, 01:31 PM
Francis Mariani
Harry, glad it worked out.


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
November 15, 2007, 02:16 PM
Harry M Cleveland
Nothing like having a redefinition of logic after you got something working ...

Rather than MAX, I have to determine the MAX and look for a tie. That is, if two of date fields are the same (for the most recent date prior to the seed date), I have to enter the value of 'checked'.

So, this code will not work:

COMPUTE MAX_CODE/A10 =
IF DATE1 EQ MAX_DATE THEN PREV_U_CODE1 ELSE
IF DATE2 EQ MAX_DATE THEN PREV_U_CODE2 ELSE
IF DATE3 EQ MAX_DATE THEN PREV_U_CODE3 ELSE PREV_U_CODE4;

Is there an easy way to sort the values of the 4 'separate' date fields and then do LAST logic?

Other than hammer and pound, I am not seeing an easy way.

Any ideas?

Thanks


FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS
November 15, 2007, 02:29 PM
Francis Mariani
quote:
if two of date fields are the same (for the most recent date prior to the seed date),
- I need a drink!

How about:

COMPUTE T1/I1 = IF DATE1 EQ MAX_DATE THEN 1 ELSE 0;
COMPUTE T2/I1 = IF DATE2 EQ MAX_DATE THEN 1 ELSE 0;
COMPUTE T3/I1 = IF DATE3 EQ MAX_DATE THEN 1 ELSE 0;
COMPUTE T4/I1 = IF DATE4 EQ MAX_DATE THEN 1 ELSE 0;

COMPUTE CODE/A10 = IF (T1 + T2 + T3 + T4) GT 1 THEN 'CHECKED' ELSE '';



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
November 15, 2007, 04:57 PM
Harry M Cleveland
Francis,

If this works, I will buy you a drink!

Thanks,

Harry


FOCUS 7.3.4 on Z/OS
WebFOCUS/EDA 7.1.8 self-service - Win2003 and Z/OS