How to aggregate hold files?

A simple routine makes multiple passes through a large data file extracting multiple (30-40) small hold files that each contain the same type of data in the same format.

Is there a way to aggregate the many hold files into one without doing join after join after join?

Thanks!

codermonkey

Platinum Member

posted

Hide Post

If the fields and formats are identical, have you considered doing a filedef with an append?

Here's an example.( I found this on Tech Support a while ago):

DEFINE FILE CAR
UNIQUE_FLAG/A1 = '1';
END
FILEDEF XYZ DISK xyz.ftm
TABLE FILE CAR
SUM RCOST BY COUNTRY BY CAR BY UNIQUE_FLAG
ON TABLE HOLD AS XYZ FORMAT ALPHA
END
-RUN
DEFINE FILE CAR
UNIQUE_FLAG/A1 = '2';
END
FILEDEF XYZ DISK xyz.ftm (APPEND
TABLE FILE CAR
SUM DCOST BY COUNTRY BY CAR BY UNIQUE_FLAG
ON TABLE SAVE AS XYZ
END
TABLE FILE XYZ PRINT *
END

A couple important things to remember:
1) be sure to put the (APPEND on the second FILEDEF or you will ovelay the first file.
2) USe SAVE (instead of HOLD) in all passes except the first.
3) Be sure to use the same name (above XYZ) in all passes.

Hope this helps.

Posts: 118 | Location: DC | Registered: May 13, 2005

Ignored post by codermonkey posted

Show Post

reFOCUSing

Guru

posted

Hide Post

Would McGyver work for you? Check the tech support site for more information.

Check this out.

Posts: 406 | Location: Canada | Registered: May 31, 2004

Ignored post by reFOCUSing posted

Show Post

j.gross

Virtuoso

posted

Hide Post

If the subsets of source records contributing to each of the extracts are disparate, so that you can define a single destination for each record of interest, you can boil the data down once, including a sort column, and then slice that small file into the appropriate pieces.

DEFINE FILE source
DEST/I2=IF ... THEN 1
ELSE IF ... THEN 2
...
ELSE 0;
END
TABLE FILE source
SUM ... ... ...
BY DEST
BY ...
ON TABLE HOLD
END
-REPEAT nextf FOR &F FROM 1 TO ...;
TABLEF FILE HOLD
PRINT ...
IF DEST EQ &F
ON TABLE HOLD AS RESULT&F
END
-nextf

This message has been edited. Last edited by: <Mabel>,

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Show Post

Platinum Member

posted

Hide Post

Try universal concatenation (more command).

3 hold files created with the same format:
-------------------------------------------------
TABLEF FILE CAR
PRINT CAR MODEL DEALER_COST
IF CAR EQ JAGUAR
ON TABLE HOLD AS HOLD1
END
TABLEF FILE CAR
PRINT CAR MODEL DEALER_COST
IF CAR EQ 'ALFA ROMEO'
ON TABLE HOLD AS HOLD2
END
TABLEF FILE CAR
PRINT CAR MODEL DEALER_COST
IF CAR NE 'ALFA ROMEO' OR JAGUAR
ON TABLE HOLD AS HOLD3
END
---------------------------------------------
Universal concatenation of the 3 hold files
to aggregate cost:

TABLE FILE HOLD1
SUM DEALER_COST BY CAR
MORE
FILE HOLD2
MORE
FILE HOLD3
END

The results of an agregation of cost:

CAR DEALER_COST
--- -----------
ALFA ROMEO 16,235
AUDI 5,063
BMW 49,500
DATSUN 2,626
JAGUAR 18,621
JENSEN 14,940
MASERATI 25,000
PEUGEOT 4,631
TOYOTA 2,886
TRIUMPH 4,292

Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004

Ignored post by ET posted

Show Post

Please Wait. Your request is being processed...