I have created 2 hold files that hold the same fields in each one except one hold information for a prior year verses the other holds information for the previous month. I want to combine these two then get all the duplicates found in the previous year that match with the previous month. But everytime I do a match or join I only get information for one or the other. I hope this makes sense. Any help would be greatly appriciated.

Thank you.

Try Universal Concatenation, i.e. the MORE command. That should do what you want.

Did you try OLD-AND-NEW in a MATCH?

I tried every single one in the match with no luck. I will look into the Universal Concatenation GinnyJakes is suggesting next. :-)

MATCH puts things side-by-side. MORE stacks things. Since you have identical masters, MORE will work best.

There are other operating system things that I could suggest later if that doesn't work for you, Latigresa.

...or you could append the 2 hold files:

FILEDEF HCAR DISK HCAR.FTM
-RUN

TABLE FILE CAR
PRINT 
CAR
BY COUNTRY
BY MODEL
WHERE COUNTRY EQ 'W GERMANY'
ON TABLE HOLD AS HCAR
END
-RUN

FILEDEF HCAR DISK HCAR.FTM (APPEND
-RUN

TABLE FILE CAR
PRINT 
CAR
BY COUNTRY
BY MODEL
WHERE COUNTRY EQ 'ITALY'
ON TABLE HOLD AS HCAR
END
-RUN

TABLE FILE HCAR
PRINT *
END

Sean

I have not had any luck. No matter what I have tried I still only get the results for one of the 2 tables. I am going to call in instead. Thanks everyone.

:-)

I guess you there's an issue with the format of hold files. When you try to concatenate 2 holds with mismatch in format.. You will retrieve only first hold file data.

Try using this

?FF HOLD1
?FF HOLD2
-RUN
Make sure the formats match & next try MORE statement.

If you post the fex code we might be able to help you better.
But the code in between [CODE[ [/CODE[ the last characters [ should be ], for better readability.
The format of the hold files is important. (focus, flatfiles) If you want to join you need the correct matching keyfields.

Here is the code that I finally got a little bit of the correct results I need but I'm still missing all the ones for the prior year that match what is in the current month. I'm able to find out which VIN # are duplicated this way but I need to get all of the claim #s for the ones in the prior year. I hope I have not confused you more.

APP HOLD ANA
MATCH FILE HOLD_CL_MONTHLY_VIN
BY VEH_ID_NO
BY CLM_NO
BY CLMNT_TYPE
BY INSD_NAME
BY DCM_NO
BY ADJ_EMP_NO
BY CLMNT_NO
BY DT_OF_ACC
RUN
FILE HOLD_ALL_VINS
BY VEH_ID_NO
BY CLM_NO
BY CLMNT_TYPE
BY INSD_NAME
BY DCM_NO
BY ADJ_EMP_NO
BY CLMNT_NO
BY DT_OF_ACC
AFTER MATCH HOLD AS AMI_TEST_CL OLD-OR-NEW
END
TABLE FILE AMI_TEST_CL
SUM
'CNT.AMI_TEST_CL.AMI_TEST.VEH_ID_NO' AS 'VIN_COUNT'
BY 'AMI_TEST_CL.AMI_TEST.VEH_ID_NO'
PRINT
'AMI_TEST_CL.AMI_TEST.CLM_NO'
'AMI_TEST_CL.AMI_TEST.CLMNT_TYPE'
'AMI_TEST_CL.AMI_TEST.CLMNT_NO'
'AMI_TEST_CL.AMI_TEST.DT_OF_ACC'
BY 'AMI_TEST_CL.AMI_TEST.VEH_ID_NO'
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_MATCH_ALL FORMAT FOCUS INDEX AMI_TEST_CL.AMI_TEST.VEH_ID_NO
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
ENDSTYLE
END
TABLE FILE HOLD_MATCH_ALL
PRINT
'HOLD_MATCH_ALL.SEG01.VEH_ID_NO'
'HOLD_MATCH_ALL.SEG01.VIN_COUNT'
'HOLD_MATCH_ALL.SEG02.CLM_NO'
'HOLD_MATCH_ALL.SEG02.CLMNT_TYPE'
'HOLD_MATCH_ALL.SEG02.CLMNT_NO'
'HOLD_MATCH_ALL.SEG02.DT_OF_ACC'
WHERE ( HOLD_MATCH_ALL.SEG02.DT_OF_ACC EQ '20071101' OR '20071102' OR '20071103' OR '20071104' OR '20071105' OR '20071106' OR '20071107' OR '20071108' OR '20071109' OR '20071110' OR '20071111' OR '20071112' OR '20071113' OR '20071114' OR '20071115' OR '20071116' OR '20071117' OR '20071118' OR '20071119' OR '20071120' OR '20071121' OR '20071122' OR '20071123' OR '20071124' OR '20071125' OR '20071126' OR '20071127' OR '20071128' OR '20071129' OR '20071130' ) AND ( HOLD_MATCH_ALL.SEG01.VIN_COUNT GT 1 );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_MATCH_ALL_1 FORMAT FOCUS INDEX HOLD_MATCH_ALL.SEG01.VEH_ID_NO



Thank you very much.
Ana

Ana,

MATCH is not going to work for you. MATCH puts the contents of two files side-by-side, not one after the other which is what you want.

Since you did an APP HOLD, could you please post the masters for HOLD_CL_MONTHLY_VIN and for HOLD_ALL_VINS so that we can see them?

Once you have the files stacked, then you can do data comparisons.

quote:

WHERE ( HOLD_MATCH_ALL.SEG02.DT_OF_ACC EQ '20071101' OR '20071102' OR '20071103' OR '20071104' OR '20071105' OR '20071106' OR '20071107' OR '20071108' OR '20071109' OR '20071110' OR '20071111' OR '20071112' OR '20071113' OR '20071114' OR '20071115' OR '20071116' OR '20071117' OR '20071118' OR '20071119' OR '20071120' OR '20071121' OR '20071122' OR '20071123' OR '20071124' OR '20071125' OR '20071126' OR '20071127' OR '20071128' OR '20071129' OR '20071130' )

It was not your question but how about:

WHERE ( EDIT(HOLD_MATCH_ALL.SEG02.DT_OF_ACC,'999999') EQ '200711';

it is a lot shorter

Or

WHERE ( HOLD_MATCH_ALL.SEG02.DT_OF_ACC LIKE '200711$*');

And there are more solutions if this field is a smartdate.

Ana,
Since you are matching on this field:

quote:

BY DT_OF_ACC

you are not really putting your data into one record. Your 2007 data for a VIN will be in one reocrd and your 2006 will be in another. You are then eliminating the 2006 records in your where. If I am understanding the problem, you want all Nov 2007 data and all 2006 where the VIN is in the Nov file. Make sure that file HOLD_ALL_VINS is indexed on VEH_ID_NO. Then try:
[code]
SET ALL=PASS
JOIN VEH_ID_NO IN HOLD_CL_MONTHLY_VIN TAG MO TO ALL VEH_ID_NO IN HOLD_ALL_VINS TAG YR AS J1
TABLE FILE HOLD_CL_MONTHLY_VIN
PRINT
YR.CLM_NO
YR.CLMNT_TYPE
(etc)
BY MO.VEH_ID_NO
BY MO.CLM_NO
(etc)
END
[\code]
TAG is optional, but it will let you designate which file the fields are coming from without having to use the entire filename as a prefix. The code is simplified, you will need to figure out what you want from there. I would recommend that you try it pretty much the way it is written (without the etc) so that you can see if you are getting the records you want as output and then expand on it.

Thanks everyone!!! I have gotten the results I needed. Hope everyone has a great day!

Ana