Is there a way to determine the final record within a sort? I need something like "NEXT", but I am writing a report.

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

Do you need ONLY "the final record within a sort" or do you need all the rows but also know the column values of the last row?

You can get the last record in a sort with a multiverb request.

TABLE FILE CAR
SUM MAX.CAR AS 'LAST_CAR'
BY COUNTRY
COUNT MODELS
BY COUNTRY
BY CAR
END

But what do you want to do with it ?

I need to calculate a value for the last record in the sort. I need all the detail prior to it.

Here is the data:

Key Start End
15 1/1/2000 9/26/2008
15
15 12/15/2008 1/11/2009
15 2/7/2009 2/9/2009
15
16...
17...

For the last line with key 15, I need to calulate 2/10/2009 for the Start and 12/31/2020 (a fixed way in the future date) for the End

Gay, why is data in the second record for key 15 missing? is that what your actual information looks like?

For some reason I expected that only the last record within the set of a given Key would have the missing pieces and that way it would be a little bit easier to implement something ...

What is 2/10/2009 based on? That date is nowhere in the data...

Francis, I think it would potentially be LAST.END_DATE + 1.

Something like this might help if and ONLY if the last record is the one with MISSING date values (that second record is still bugging me):

TABLE FILE <table>
PRINT
      START_DT AS 'Actual,Start'
      END_DT   AS 'Actual,End'
COMPUTE NEW_START_DT/YYMD = IF START_DT EQ MISSING AND REC_KEY EQ LAST.REC_KEY THEN DATEADD(LAST.END_DT, 'D', 1) ELSE START_DT; AS 'New,Start'
COMPUTE NEW_END_DT/YYMD = IF END_DT EQ MISSING AND REC_KEY EQ LAST.REC_KEY THEN 20201231 ELSE START_DT; AS 'New,End'
BY REC_KEY
BY START_DT
END

Wait a minute ... do MISSING values appear first or last when sorted? I'll have to test that ...

I think Waz's multi-verb suggestion will still work with some minor changes:

TABLE FILE filename
  SUM MAX.END AS 'MAX_END'
  AND COMPUTE LAST_START/MDYY = MAX_END + 1 ;
  AND COMPUTE LAST_END/MDYY = '12/31/2020';
   BY KEY
PRINT START
      END
   BY KEY
END

If you need the generated records to become part of the existing data, then a different approach is needed:

TABLE FILE filename
  SUM MAX.END AS 'MAX_END'
   BY KEY
   ON TABLE HOLD
END
-*
DEFINE FILE HOLD
 START/MDYY = MAX_END + 1 ;
 END/MDYY  = '12/31/2020';
END
-*
TABLE FILE HOLD
 PRINT START
       END
    BY KEY
MORE
FILE filename
END

Can you live with the calculation being done on all blank dates? If so, consider the following code. Note that I took some liberties with the data for ease of reporting. Also note that you can alter the COMPUTEs for the NEW_COLx fields. I was concentrating on the functionality.

APP FI FclPntTst DISK FclPntTst.MAS (LRECL 80
-RUN
-WRITE FclPntTst
-WRITE FclPntTst FILE=FclPntTst,SUFFIX=FOC
-WRITE FclPntTst SEGNAME=SEG1, SEGTYPE=S0
-WRITE FclPntTst FIELD=COL1,A2,A2,$
-WRITE FclPntTst FIELD=COL2,DATE,A8,$
-WRITE FclPntTst FIELD=COL3,DATE,A8,$
-RUN
CREATE FILE FclPntTst
MODIFY FILE FclPntTst
FIXFORM COL1/A2 COL2/A8 COL3/A8
DATA
152000010120080926
15                
152008121520090111
152009020720090209
15                
161981091219812010
171999010120101231
END
-RUN
TABLE FILE FclPntTst
PRINT COL1 
COL2
COL3
COMPUTE NEW_COL2/A10 = IF COL2 EQ '' THEN '19000101' ELSE COL2 ;
COMPUTE NEW_COL3/A10 = IF COL3 EQ '' THEN '20121231' ELSE COL3 ;
ON TABLE SET PAGE OFF
END

So, yeah, the NEXT function would be nice to have. But...

I am at home without easy access to dev studio, so I may have made a syntax error,
but I believe you can at least get the idea.

quote:

DEFINE FILE CAR
TOT_CTR/I8 WITH MODEL = 1;
END

TABLE FILE CAR
SUM
TOT_CTR NOPRINT
SUM
COMPUTE CTR/I8 = LAST CTR + 1; NOPRINT
COMPUTE MSG/A30 = IF TOT_CTR EQ CTR THEN 'THIS IS THE LAST RECORD' ELSE ' ';
BY COUNTRY
BY CAR
END

OK... When you get a chance, try this: Hold the count of models by country, join that to the car file and get "YES" for the Last Record in your sort (Country) ("no" for the rest)

TABLE FILE CAR
SUM CNT.MODEL AS 'TotalCars'
BY COUNTRY
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS CARCOUNT
END
JOIN COUNTRY IN CARCOUNT TO COUNTRY IN CAR AS J1
TABLE FILE CARCOUNT
SUM
COMPUTE Counter/I8 WITH COUNTRY = IF COUNTRY EQ LAST COUNTRY THEN Counter + 1 ELSE 1; NOPRINT
-* LastRec is set to YES if it's the Last Record in the COUNTRY sort (Counter = TotalCars)
COMPUTE LastRec/A3 = IF Counter EQ TOTALCARS THEN 'YES' ELSE 'no'; AS 'Last,Record'
Counter AS 'Car,Cntr'
TOTALCARS AS 'Tot,Cars'
BY COUNTRY
BY CAR
BY MODEL
ON TABLE SET PAGE OFF
END

I left the "Counter AS 'Car,Cntr' and TOTALCARS AS 'Tot,Cars'" in there just for visual analysis...

Let us know how it goes... And how my post from Thu Feb 25 2010 20:49 fits...
Have a great Weekend... I gotta get out of here...

njsden was right. The original data is a set of assignments by date for each person (the key points to a person). I first extract the actual assignments for each person then the data I present is the "gaps" where a person could be assigned work. The gap is calculated as the date between assignments so the start is the last start date + 1 day. The final record end date will be a far in the future date.

The reason there is no data for the second record is because there is no gap in the assignments.

I was off Friday and didn't see your replies. I will go through them today. Thanks for your help.

(FYI, I am working with a system called ClickSchedule that assigns engineers to jobs.).

Thanks to all, I tried Dan's code (the second set), and it worked fine. I had to set ASNAMES on.

Gary