I need to pull the last 5 gifts and display the record on one line instead of each line. The only problem is if I use the Across sort, there is no way for me to grab the last 5 gifts. Its using the same field (GIFT_AMT)

Tracie,

Use the LIST verb, then, create your columns, i.e.,

LIST
GIFT_AMT
BY KEYSORTCOLUMN
ON TABLE HOLD AS H1
END

DEFINE FILE H1
COL1/P15.2C = IF LIST EQ 1 THEN GIFT_AMT ELSE 0;
COL2/P15.2C = IF LIST EQ 2 THEN GIFT_AMT ELSE 0;
COL3/P15.2C = IF LIST EQ 3 THEN GIFT_AMT ELSE 0;
ETC......
END
TABLE FILE H1
SUM
COL1
COL2
ETC....
BY KEYSORTCOLUMN
IF LIST LE 5

I know you are aware of HIGHEST/LOWEST; and I am positive can do the rest...

Frank should be along, shortly....

EDIT: TYPO....

This message has been edited. Last edited by: Tom Flynn,

Hi Tracie,

I may not understand what you need, but here's an example using test data that displays the last five gifts sorted by date for the four accounts in the test file.

-*
-*** THIS CREATES A TEST FILE AND TEST DATA
-*
FILEDEF T_MAS DISK T_TAB.MAS
FILEDEF T_TAB DISK T_TAB.TXT
-RUN
-*
-WRITE T_MAS FILE=TESTTAB,SUFFIX=FIX
-WRITE T_MAS SEGNAME=SEG1
-WRITE T_MAS FIELD=T_ACCT,,A04,A04,$
-WRITE T_MAS FIELD=T_FIL1,,A01,A01,$
-WRITE T_MAS FIELD=T_DATE,,YYMD,A08,$
-WRITE T_MAS FIELD=T_FIL2,,A01,A01,$
-WRITE T_MAS FIELD=T_GIFT,,D12.2,A12,$
-*
-WRITE T_TAB 0001 20070701 000000500.00
-WRITE T_TAB 0001 20070601 000005500.00
-WRITE T_TAB 0001 20060801 000000530.00
-WRITE T_TAB 0001 20070201 000000130.00
-WRITE T_TAB 0001 20070801 000000100.00
-WRITE T_TAB 0001 20070811 000000050.00
-WRITE T_TAB 0001 20070101 000000025.00
-WRITE T_TAB 0001 20070215 000010500.00
-*
-WRITE T_TAB 0002 20070521 000000150.05
-WRITE T_TAB 0002 20070501 000000123.12
-WRITE T_TAB 0002 20070515 000000160.00
-WRITE T_TAB 0002 20070301 000000550.00
-WRITE T_TAB 0002 20070401 000000200.00
-WRITE T_TAB 0002 20070810 000004200.00
-WRITE T_TAB 0002 20070801 000000210.00
-*
-WRITE T_TAB 0101 20070701 000010500.00
-WRITE T_TAB 0101 20070219 000025500.00
-WRITE T_TAB 0101 20070301 000000501.01
-WRITE T_TAB 0101 20070401 000060130.00
-WRITE T_TAB 0101 20070601 000000700.70
-WRITE T_TAB 0101 20070611 000003050.30
-WRITE T_TAB 0101 20070101 000009025.99
-WRITE T_TAB 0101 20070215 000025500.25
-WRITE T_TAB 0101 20070105 000032100.05
-*
-WRITE T_TAB 0901 20070805 000000020.00
-WRITE T_TAB 0901 20070801 000000015.00
-*
-*** THIS SORTS TEST DATA BY ACCOUNT BY DATE OF GIFT
-*
DEFINE FILE T_TAB
A_GIFT/A15 = FTOA(T_GIFT,'(D12.2)',A_GIFT);
CNT/I3 = 1;
END
-*
TABLE FILE T_TAB
SUM CNT
BY T_ACCT
PRINT
COMPUTE CNTR/I3 = IF T_ACCT EQ LAST T_ACCT THEN CNTR + 1 ELSE 1;
T_GIFT
A_GIFT
BY T_ACCT
BY T_DATE
ON TABLE HOLD AS HOLD1
END
-*
-*** THIS CREATES A FILE OF THE LAST 5 GIFTS
-*
TABLE FILE HOLD1
PRINT *
WHERE ( CNTR GE (CNT - 4) )
ON TABLE HOLD AS HOLD2
END
-*
-*** THIS CREATES ONE RECORD PER ACCOUNT AND THE LAST 5 GIFTS(L5_GIFTS)
-*
DEFINE FILE HOLD2
L5_GIFTS/A91 = IF T_ACCT EQ LAST T_ACCT
THEN SUBSTR(91, L5_GIFTS, 1, 72, 72, 'A72') || ' <> ' || A_GIFT
ELSE A_GIFT;
END
-*
TABLE FILE HOLD2
SUM
L5_GIFTS
BY T_ACCT
END

This is a multiple pass solution and may not be of any use, but it is one idea.

Jim

Jim,

Using your data, with this code:

TABLE FILE TESTDATA
LIST
T_GIFT
BY T_ACCT
BY HIGHEST T_DATE
ON TABLE HOLD AS H1
END
-RUN
DEFINE FILE H1
COL1/P15.2C = IF LIST EQ 1 THEN T_GIFT ELSE 0;
COL2/P15.2C = IF LIST EQ 2 THEN T_GIFT ELSE 0;
COL3/P15.2C = IF LIST EQ 3 THEN T_GIFT ELSE 0;
END
TABLE FILE H1
SUM
COL1
COL2
COL3
BY T_ACCT
IF LIST LE 3
END
-EXIT


produced:


PAGE 1

T_ACCT COL1 COL2 COL3
0001 50.00 100.00 500.00
0002 4,200.00 210.00 150.05
0101 10,500.00 3,050.30 700.70
0901 20.00 15.00 .00


Just FYI - many ways to climb the WF mountain...

Perfect!! Thanks so much!

This is how I used the code. I am actually trying to display 3 pieces of info for each of the 5 gifts. But its displaying the amounts, but not the dates and designations. If I change the SUM to print, it appears on separate lines....


DEFINE FILE AA_GIFT_DETAIL
GIFTDATE/MDYY=HDATE(GIFT_DATE, 'MDYY');

END
TABLE FILE AA_GIFT_DETAIL
LIST
GIFT_AMT
GIFTDATE
DESIGNATION_NAME
FISC_CODE
BY ID
BY HIGHEST GIFT_DATE
WHERE PREF_DONR_CODE EQ 'ALUM' OR 'ALND';
WHERE RECORDLIMIT LE 500
ON TABLE HOLD AS H1
END
DEFINE FILE H1
COL1/P15.2C = IF LIST EQ 1 THEN GIFT_AMT ELSE 0;
COL2/MDYY = IF LIST EQ 1 THEN GIFTDATE ELSE ' ';
COL3/A150 = IF LIST EQ 1 THEN DESIGNATION_NAME ELSE ' ';
COL4/P15.2C = IF LIST EQ 2 THEN GIFT_AMT ELSE 0;
COL5/MDYY = IF LIST EQ 2 THEN GIFTDATE ELSE ' ';
COL6/A150 = IF LIST EQ 2 THEN DESIGNATION_NAME ELSE ' ';
COL7/P15.2C = IF LIST EQ 3 THEN GIFT_AMT ELSE 0;
COL8/MDYY = IF LIST EQ 3 THEN GIFTDATE ELSE ' ';
COL9/A150 = IF LIST EQ 3 THEN DESIGNATION_NAME ELSE ' ';
COL10/P15.2C = IF LIST EQ 4 THEN GIFT_AMT ELSE 0;
COL11/MDYY = IF LIST EQ 4 THEN GIFTDATE ELSE ' ';
COL12/A150 = IF LIST EQ 4 THEN DESIGNATION_NAME ELSE ' ';
COL13/P15.2C = IF LIST EQ 5 THEN GIFT_AMT ELSE 0;
COL14/MDYY = IF LIST EQ 5 THEN GIFTDATE ELSE ' ';
COL15/A150 = IF LIST EQ 5 THEN DESIGNATION_NAME ELSE ' ';
END
TABLE FILE H1
SUM
COL1 AS 'GIFTAMT1'
COL2 AS 'GIFTDATE1'
COL3 AS 'DESGNAME1'
COL4 AS 'GIFTAMT2'
COL5 AS 'GIFTDATE2'
COL6 AS 'DESGNAME2'
COL7 AS 'GIFTAMT3'
COL8 AS 'GIFTDATE3'
COL9 AS 'DESGNAME3'
COL10 AS 'GIFTAMT4'
COL11 AS 'GIFTDATE4'
COL12 AS 'DESGNAME4'
COL13 AS 'GIFTAMT5'
COL14 AS 'GIFTDATE5'
COL15 AS 'DESGNAME5'
BY ID

IF LIST LE 5
END

Tracie,

Use MAX. for your MDYY and A150 columns:

TABLE FILE H1
SUM
COL1 AS 'GIFTAMT1'
MAX.COL2 AS 'GIFTDATE1'
MAX.COL3 AS 'DESGNAME1'
COL4 AS 'GIFTAMT2'
MAX.COL5 AS 'GIFTDATE2'
MAX.COL6 AS 'DESGNAME2'
COL7 AS 'GIFTAMT3'
MAX.COL8 AS 'GIFTDATE3'
MAX.COL9 AS 'DESGNAME3'
COL10 AS 'GIFTAMT4'
CMAX.OL11 AS 'GIFTDATE4'
MAX.COL12 AS 'DESGNAME4'
COL13 AS 'GIFTAMT5'
MAX.COL14 AS 'GIFTDATE5'
MAX.COL15 AS 'DESGNAME5'
BY ID

IF LIST LE 5
END


Tom

IT WORKS! THANKS SO MUCH!

Welcome....

I can logoff now!!!