This feature always has a way of confusing me. I have read the many posts that include trying multiple things such as:

SET CARTESIAN = ON
SET NODATA = 'N/A'
SET ALL = PASS

And with any combination I cannot get it right.

I have 2 hold files that have a list of people with a corresponding id. Some people can be missing in file1 and be in file2 or be missing in file2 and be in file1. I want to match these so that their id's will line up in each row. For example:

File1
1 John Doe
1 Jane Doe
2 Bob Gibson
2 Sandy Gibson
2 Charlie Gibson
3 Shiela Scott
4 Amy Filter
4 Brad Filter

File2
1 John Doe
1 Jane Doe
2 Bob Gibson
2 Sandy Gibson
3 Shiela Scott
4 Amy Filter
4 Brad Filter
5 Sammy Davis
5 Natalie Davis

I do this:

MATCH FILE File1
BY ID
BY LN
BY FN
RUN
FILE File2
BY DID
BY DLN
BY DFN
AFTER MATCH HOLD AS MATCHDEP OLD-AND-NEW
END

I use old and new just so i can see if they are lining up correctly and they are not. They line up like this (because I do them in a BY field that would mean they would be alphabetical)

ID FN LN DID DFN DLN
1 John Doe 1 John Doe
1 Jane Doe 1 Jane Doe
2 Bob Gibs 2 Bob Gibs
2 Sal Gibs 2 Sal Gibs
2 Kay Gibs 3 May Scot
3 May Scot 4 Amy Adam
4 Amy Adam 4 Brad Adam
4 Brad Adam -*this line is blank...

Why does it not line up correctly and how can I fix this to put the blank instead next to the row with Charlie Gibson.

(all names are made up)

Thank you

This message has been edited. Last edited by: slfmr,

Unless your 2 files have been saved as FORMAT FOCUS INDEX ID LN FN, then they have to be saved with the same sort order ... BY ID BY LN BY FN

And if you want the lines with no match, then you will need to use OLD-OR-NEW.

Okay I have tried that and this is what I have and it is still not lining them up correctly:

TABLE FILE File1
PRINT *
ON TABLE HOLD AS F1 FORMAT FOCUS INDEX ID LN FN
END

TABLE FILE File2
PRINT *
ON TABLE HOLD AS F2 FORMAT FOCUS INDEX DID DLN DFN
END

MATCH FILE F1
BY ID
BY LN
BY FN
RUN
FILE F2
BY DID
BY DLN
BY DFN
AFTER MATCH HOLD AS MATCHDEP OLD-OR-NEW
END

TABLE FILE MATCHDEP
PRINT *
END
-EXIT

Is something wrong here?

I'm not sure I'm clear on exactly what is happening here, but I have never been able to get a match to work properly unless the by fields are identically named. As in:

TABLE FILE File1
PRINT *
ON TABLE HOLD AS F1 FORMAT FOCUS INDEX ID LN FN
END

TABLE FILE File2
PRINT *
ON TABLE HOLD AS F2 FORMAT FOCUS INDEX DID DLN DFN
END

MATCH FILE F1
BY ID
BY LN
BY FN
RUN
FILE F2
BY DID AS ID
BY DLN AS LN
BY DFN AS FN
AFTER MATCH HOLD AS MATCHDEP OLD-OR-NEW
END

TABLE FILE MATCHDEP
PRINT *
END


Notice the 3 AS clauses. If this does not work, I'd be interested to see what results you do get.

What is happening is say I have a list of 10 ssns in a table with the persons first and last name (this becomes Hold File1). I need to check another table and verify that the first and last name match what is in the first hold file1 (so I create a hold file2).

What is happening is there are some ssns in file1 that are NOT in file2 and vice versa, so when I do a match, all the match does for me is place these hold files (tables) side by side without making sure that ssn in file 1 is in the same row as ssn in file 2.

In turn, the rows are off and some names do not match when they are actually in the table. Does that make sense?

I am going to try to work around this.. but any ideas would be helpful.

I did try the:
BY DID AS ID

and made sure the AS names matched the File1 names, but that didn't work. I will check my code again.

Thank you!

Ok, why not this:

SET ALL=ON

JOIN SSN IN FILE1 TO DSSN IN FILE2 AS X

TABLE FILE FILE1
PRINT
SSN
FN
LN
DFN
DLN
END

If you just want to see the non-matches, you could compare LN to DLN and FN to DFN.

The creating reports manual has a good section on MATCH:
http://documentation.informationbuilders.com/masterinde...ang/wf_crlang_53.pdf

Page 15-3 talks about fine tuning MATCH processing and the different outcome based on the verb objects used.

Because you are not explicitly stating verb objects I think FOCUS assumes the PRINT verb. I've never gotten what I expected/needed with a match using PRINT and PRINT.

Have you also verified that the FN and LN fields are the same length and format? They need to be if you want the results to match up. Things that might trip you up -- char vs. varchar, trailing spaces, etc.

If the JOIN with ALL=ON doesn't resolve the problem try something like this:

MATCH FILE F1
-* changing to SUM has no impact to output if
-* only LN and FN per ID in F1
SUM MAX.LN MAX.FN
BY ID
RUN
FILE F2
PRINT DLN DFN
BY DID AS ID
AFTER MATCH HOLD AS MATCHDEP OLD-OR-NEW
END


Good luck!

Oh, that's funny! None of us noticed that there was no SUM verb? I'm pretty sure codemonkey's idea is going to work for you.

Great thanks!

I will try these out and take a look at the manuel and see how I fare.

Again, thank you so much!