Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Matching
 Login/Join
 
Platinum Member
posted
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,


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6
 
Posts: 178 | Registered: May 11, 2005Report This Post
Silver Member
posted Hide Post
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.
 
Posts: 40 | Registered: March 10, 2004Report This Post
Platinum Member
posted Hide Post
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?


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6
 
Posts: 178 | Registered: May 11, 2005Report This Post
Platinum Member
posted Hide Post
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.


dwf
 
Posts: 135 | Location: Portland, OR | Registered: March 23, 2005Report This Post
Platinum Member
posted Hide Post
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!


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6
 
Posts: 178 | Registered: May 11, 2005Report This Post
Platinum Member
posted Hide Post
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.


dwf
 
Posts: 135 | Location: Portland, OR | Registered: March 23, 2005Report This Post
Platinum Member
posted Hide Post
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!
 
Posts: 118 | Location: DC | Registered: May 13, 2005Report This Post
Platinum Member
posted Hide Post
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.


dwf
 
Posts: 135 | Location: Portland, OR | Registered: March 23, 2005Report This Post
Platinum Member
posted Hide Post
Great thanks!

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

Again, thank you so much!


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6
 
Posts: 178 | Registered: May 11, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders