Hello,

Sorry if this type of question was asked before. I'm tring to extract specific values by using a MATCH FILE command.

Say for example the two tables are ALPHA and BETA The information I want to extract is where the asterisks are.

This is a m:m relationship.

Data Table ALPHA
DATE....A...B....EXTRA
01........1...1.......-
01........2...1.......-
01........2...2.......- (----- ** (capture this file)
01........3...2.......-
02........1...1.......-
02........1...2.......-
02........2...1.......-


Data Table BETA
DATE....A...B...EXTRA
01........1...1.......-
01........2...1.......-
01........3...2.......-
02........1...1.......-
02........1...2.......-
02........1...3.......- (----- ** (capture this file)
02........2...1.......-

(the field extra contains extra data that I am trying to capture)

This is the code I am using:

MATCH FILE ALPHA
PRINT EXTRA
BY DATE
BY A
BY B
RUN

FILE BETA
PRINT EXTRA
BY DATE
BY A
BY B
AFTER MATCH HOLD OLD-NOR-NEW
RUN

TABLE FILE HOLD
PRINT *
END

My code doesn't work. My end result is that I'm getting all records from both tables.
Does anyone know how I can resolve this, or another technique that I can use?

Thank you in advance!

Assuming there is only one value of extra per DATE, A, and B combination, try changing the PRINT command to a SUM command.

Just to confirm there will only be one value in field extra, per Date/field A/field B.

Unfortunately I still get the same results

Mirza, this is the code that I used, and my answer. Hope it helps.
MATCH FILE ADATA
WRITE EXTRA BY DATE BY AFLD BY BFLD
RUN
FILE BDATA
WRITE EXTRA BY DATE BY AFLD BY BFLD
AFTER MATCH HOLD OLD-NOR-NEW
END
DATE AFLD BFLD EXTRA EXTRA
---- ---- ---- ----- -----
1 2 2 A
2 1 3 B B

Hi Noreen,

That didn't work either. I removed the first BY statement (BY DATE) and moved it into the PRINT/WRITE statement. The file produced the type of results I was looking for. Although I tested this for a small sample set .. if I ran this report for a large sample set I may get inaccurate results.

This is what my code looks like.
MATCH FILE ADATA
WRITE EXTRA DATE
BY AFLD BY BFLD
RUN
FILE BDATA
WRITE EXTRA DATE
BY AFLD BY BFLD
AFTER MATCH HOLD OLD-NOR-NEW
END

Would it matter if my DATE fields (from the data sources) don't have the same name? (i.e. DATE_1 and DATE_2)

Check that the fields represented by A, B, and DATE have identical names and formats in your two MFDs

If they have the same format, but just different names, then on your BY statement, rename them both to the same thing.

  BY DATE_1 AS  'DATE' 

in the first query, and

 BY DATE_2 AS 'DATE' 

in the second.
Have the setting ASNAMES ON before you do your MATCH file.

 SET ASNAMES=ON 

Wow, I can't believe that was the problem... Having the same field name DOES work.

Thanks everyone for your help!