August 16, 2004, 01:23 PM
<jrusse>Rotating data in/out of a table by columns
I'm new to Focus (and Query Languages in general). I am attempting to create a table that maintains 60 days worth of data for each employee ID as follows:
ID Day(-59) Day(-58) ...... Day(0)
Each day, I want to roll off the Day(-59) and roll on a new Day(0) and then have all intermediate days roll back another day. I am working in Standard Focus on an OS/390 mainframe. I have contemplated using the MATCH command to accomplish adding a column, but I have not figured out how to remove the Day(-59) column from the file. Furthermore, I have not figured out how to move the data under an column in the middle back to a previous day (for instance Day(-57) would now become Day(-58)).
Any suggestions would be appreciated.
August 16, 2004, 01:41 PM
GCohenI would pre-process the data something like this..
TABLE FILE X
SUM VALUE BY ID BY HIGHEST DAY
ON TABLE HOLD
END
DEFINE FILE HOLD
NEWVALUE=IF ID NE LAST ID THEN 0 ELSE LASTVALUE ;
LASTVALUE=VALUE;
END
-* MAKE UP THE DISPLAY REPORT ..SAMPLE
TABLE FILE HOLD
PRINT NEWVALUE
BY ID BY DAY
END