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!
May 26, 2005, 05:51 PM
codermonkey
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.
May 26, 2005, 05:53 PM
reFOCUSing
Would McGyver work for you? Check the tech support site for more information.
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 -nextfThis message has been edited. Last edited by: <Mabel>,
May 26, 2005, 06:38 PM
ET
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