Consolidating records in hold file

[TomBorcoman]

Hi..I have created a HOLD file that was created with 2 joins and the data is what I want, however, I have some records that are listed twice and I want to combine the 2nd or 3rd, etc. instances into one line in a new table. Here is the example:

Name ID Fund Dept Alloc%
Ehlo 3 10003 1114 60
Ehlo 3 60020 8768 40


I would like a new table with one record as follows:

Name ID Fund1 Dept1 Alloc%1 Fund2 Dept2 Alloc%2
Ehlo 3 10003 1114 60 60020 8768 40

Thanks for any help you can provide!!!

 

[focwizard]

Tom,

Sounds like you want a MATRIX type report. What you could do is DEFINE a counter with each record, such that the FIRST record has a value of 1; the second has a value of 2; etc. The code might look something like this:

DEFINE FILE filename
CTR/I5 = IF NAME NE LAST NAME THEN 1 ELSE LAST CTR + 1;
END
TABLE FILE filename
SUM ID FUND DEPT ALLOC%
BY NAME ACROSS CTR NOPRINT
END

So, for every record (assuming the records are in NAME sequence), if the NAME is different from the prior NAME, the counter restarts at 1; if the NAME is the same, just increment the counter.

You should use SUM, to aviod the 'stair step' effect. You want ALL records for a given NAME to appear on one output line.

 

[TomBorcoman]

I think that will work to give me one record for each person but would that let me create those additional fields - Fund2, Dept2, Alloc%2 that correspond to an individual record?

 

[TomBorcoman]

Guess I should have just tried it before responding! That worked focwizard...many thanks!