I am trying to match two databases based off of one field, to update another field. There are three key fields in the database and I only want to match on Social Security Number. Is this possible to do without Webfocus producing this message:

0 ERROR AT OR NEAR LINE 198 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC439) WARNING. A MATCH CONDITION HAS BEEN ASSUMED FOR: LAST_NAME

When it makes this assumption it ruins my match because the other database does not have a perfect match in all the key fields.



-Webfocus 7.6.8 on Windows.

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

That error message appears to be as a result of a MODIFY process. Explain more with some sample code between <code> and </code> tags.

T

Indeed, that's a MODIFY error message.
Unless we're mistaken, you probably need the MATCH FILE command, not the MODIFY FILE command.
Look up the documentation for the MATCH FILE command, or provide more insight in what you're trying to do.

Here is the part of my code that does the match.


MODIFY FILE HOLDSWORD2

FIXFORM FROM HOLDCBH2

MATCH SOCSECNUM
ON MATCH COMPUTE CLIENTID_IDX = 1;
DATA ON HOLDCBH2
END

ON TABLE HOLD
END


Then I see the following in the errors:

0 ERROR AT OR NEAR LINE 198 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC439) WARNING. A MATCH CONDITION HAS BEEN ASSUMED FOR: LAST_NAME
0 ERROR AT OR NEAR LINE 198 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC439) WARNING. A MATCH CONDITION HAS BEEN ASSUMED FOR: FIRST_NAME
0 WARNING..TRANSACTIONS ARE NOT IN SAME SORT ORDER AS FOCUS FILE
PROCESSING EFFICIENCY MAY BE DEGRADED

If there are three key fields in the database, then put all three in the file used as the FIXFORM input.

I presume you have a ON MATCH UPDATE field-name clause...

But I only want to match on one of the fields. I don't want the other 2 influencing the match.

If you are actually wanting to MODIFY the file, and there are 3 key fields you have to match on all of them. If you are adding a computed field to your database, use the file you want to update as your original input. What are you actually trying to accomplish?

I have two similar databases. One has good data while the others key fields are not accurate. Mostly Typos. So what I am trying to do is run an algorithm of matches that will find possible matches in the other database and update the key fields in the bad database accordingly. I dumped the data into two FTM so that I could update the key fields. Once everything was updated I was going to load the data back into the bad database thus cleaning it up.

I just saw in this post that MATCH cannot update FTM's

http://forums.informationbuild...5403/xsl/print_topic


What are my options to complete this task?


For starters I want to find matches in the database on and two key fields,
FIRSTNAME, LASTNAME, SSN or DOB

Below is the code I am using to match on SOCSECNUM as a test. In the edaprint I am getting (FOC415) over and over again until it kills the process for exceeding max messages.




APP FI DIMCLIENT1 DISK mhmr_data/dimclient1.ftm

TABLE FILE DIMCLIENT1
PRINT *
ON TABLE HOLD AS DIMCLIENTPATHX
END



DEFINE FILE DIMCLIENTPATHX
TDAY/YYMD=&YYMD;
BIRTH_DATE/YYMD = HDATE(DOB, 'YYMD');
YEARS/I3=DATEDIF(BIRTH_DATE, TDAY, 'Y');
SOCSECNUM/A9 = SSN;
SEX/A1 = IF GENDER_IDX EQ 'M' THEN '1'
ELSE IF GENDER_IDX EQ 'F' THEN '2';
END



TABLE FILE DIMCLIENTPATHX
PRINT CLIENTID_IDX SOCSECNUM LAST_NAME FIRST_NAME SEX BIRTH_DATE
WHERE YEARS GT 17
ON TABLE HOLD AS HOLDCBH
END


TABLE FILE HOLDCBH
BY CLIENTID_IDX
BY SOCSECNUM
BY LAST_NAME
BY FIRST_NAME
BY SEX
BY BIRTH_DATE
ON TABLE HOLD AS HOLDCBH2
END
-RUN


APP FI SWORDPATH1 DISK mhmr_data/swordpath1.ftm

TABLE FILE SWORDPATH1
PRINT *
ON TABLE HOLD AS SWORDPATHX
END



DEFINE FILE SWORDPATHX
TESTER/A1 = EDIT(LAST_NAME,'9') ;
CLIENTID_IDX/I11 = 0;
END


TABLE FILE SWORDPATHX
PRINT LAST_NAME FIRST_NAME BIRTH_DATE SEX RACE ETHNICITY ALAST_NAME AFIRST_NAME AREA_CODE PHONE MESSAGE_PL TRANSEXUAL SEXUAL_MINOR HEALTH_INSUR ENGLISH_SP MILITARY EMERG_LAST
EMERG_FIRST EMERG_NATURE EMERG_ADDR EMERG_CITY EMERG_AREA EMERG_PHONE VERIFIED MARKER DUMMY2 CONTACT_DATE ST_TIMEH ST_TIMEM ST_TIMEAP
FACIL_NUMBER E_TIMEH E_TIMEM E_TIMEAP OUT_LAST OUT_FIRST EWITHOTHER EWITHCHILD FIRST_CONTAC CONTACT_TYPE DID_NOT_FIND CONTACT_NUM CONTACT_ST
FACE_TO_FACE INDICATOR SUICIDAL HOMICIDAL AGGRESSIVE INTOXICATED MEDICAL_CARE FEATURES FOOD CLOTHING TRANSPORT ENGAGE_CT PSYCHIATRIC
MEDICAL POLICE BENEFITS HOUSING DASERVICE LEGAL SELFCARE SELFPRES EMPLOYVOC OTHER OTHER_SPEC ENGAGE_ONLY PLACEMENT PLACEMENT_AG PLACEMENT_NO
WHY_NO MEDICAL_ASS SSI UNEMPLOYMENT FOOD_STAMPS SOCIAL_SEC EMPLOYMENT WORKMEN_COMP VA_BENEITS VA_PENSION PENSION SELF_EMP CONTACT_DIR CLIENTID_IDX

WHERE SOCSECNUM NE ' ' OR
SOCSECNUM EQ ' ' AND LAST_NAME NE ' ' AND FIRST_NAME NE ' ' OR
SOCSECNUM EQ ' ' AND LAST_NAME NE ' ' AND BIRTH_DATE NE ' ' OR
SOCSECNUM EQ ' ' AND FIRST_NAME NE ' ' AND BIRTH_DATE NE ' '
WHERE SOCSECNUM NOT CONTAINS 'A' AND
SOCSECNUM NOT CONTAINS 'E' AND
SOCSECNUM NOT CONTAINS 'I' AND
SOCSECNUM NOT CONTAINS 'O' AND
SOCSECNUM NOT CONTAINS 'U' AND
SOCSECNUM NOT CONTAINS 'a' AND
SOCSECNUM NOT CONTAINS 'e' AND
SOCSECNUM NOT CONTAINS 'i' AND
SOCSECNUM NOT CONTAINS 'o' AND
SOCSECNUM NOT CONTAINS 'u' AND
SOCSECNUM NOT CONTAINS 'x' AND
SOCSECNUM NOT CONTAINS ' ' AND
SOCSECNUM NE '000000000' AND
SOCSECNUM NE '999999999' AND
SOCSECNUM NE '123456789' AND
SOCSECNUM NE '987654321'

WHERE (CONTACT_DATE GE 08012006 AND CONTACT_DATE LE 12012006)
BY SOCSECNUM
ON TABLE HOLD AS HOLDSWORD1
END


TABLE FILE HOLDSWORD1
PRINT BIRTH_DATE SEX CLIENTID_IDX
BY SOCSECNUM
BY LAST_NAME
BY FIRST_NAME
ON TABLE HOLD AS HOLDSWORD2
END
-RUN


MODIFY FILE HOLDSWORD2
FIXFORM FROM HOLDCBH2
MATCH SOCSECNUM
ON NOMATCH REJECT
ON MATCH UPDATE CLIENTID_IDX
DATA ON HOLDCBH2
END




JOIN SSN IN FILE1 TO ALL SSN IN FILE2 AS J0

TABLE FILE FILE1
PRINT KEY1 KEY2 KEY3 SOCSECNUM CLIENTID_IDX
ON TABLE HOLD AS HOLD1
END

MODIFY FILE FILE1
FIXFORM FROM HOLD1
MATCH KEY1 KEY2 KEY3
ON NOMATCH REJECT
ON MATCH UPDATE CLIENTID_IDX
DATA ON HOLD1
END

Modify indeed cannot update any fix type of file.
And, from the code you posted, I seem to conclude that the source files also are of a fixed type, since you define them as being of extension .ftm.
Which means that modify is out of the question here.
Which leaves the MATCH FILE command. I'm quite convinced that it can do what you need to do.

I am trying the match file command now. I will update once resolved.

I made some changes to the MATCH. MATCH FILE doesn't look like it will let me do what I want. MODIFY seems to be okay with my matching on only SOCSECNUM if it is the last BY field so I am trying that. That match is producing this error though now:


0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5
0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5
0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5
1
0 NUMBER OF RECORDS IN TABLE= 207129 LINES= 207129
0
0 NUMBER OF RECORDS IN TABLE= 9392 LINES= 9392
0 NUMBER OF RECORDS IN TABLE= 9392 LINES= 9392
(FOC415)TRANS 1 REJECTED NOMATCH HOLDSWOR
######### LASTNAM HERBERT 1 ?o
(FOC415)TRANS 2 REJECTED NOMATCH HOLDSWOR
######### LASTNAM DERRICK 1 3s
(FOC415)TRANS 3 REJECTED NOMATCH HOLDSWOR
######### LASTNAM JAHON 1 ët
(FOC415)TRANS 4 REJECTED NOMATCH HOLDSWOR
######### LASTNAM JOSEPH 1 st
(FOC415)TRANS 5 REJECTED NOMATCH HOLDSWOR
######### LASTNAM LESTER 1 E0
0 TRANSACTIONS: TOTAL = 5 ACCEPTED= 0 REJECTED= 5
SEGMENTS: INPUT = 0 UPDATED = 0 DELETED = 0



I am using the databases again and not the FTMs.




-SET &ECHO = ALL ;

TABLE FILE DIMCLIENT
PRINT *
WHERE RECORDLIMIT EQ 5
ON TABLE HOLD AS DIMCLIENTPATHX
END

DEFINE FILE DIMCLIENTPATHX
TDAY/YYMD=&YYMD;
BIRTH_DATE/YYMD = HDATE(DOB, 'YYMD');
YEARS/I3=DATEDIF(BIRTH_DATE, TDAY, 'Y');
SOCSECNUM/A9 = SSN;
SEX/A1 = IF GENDER_IDX EQ 'M' THEN '1'
ELSE IF GENDER_IDX EQ 'F' THEN '2';
LNAME/A20 = EDIT(LAST_NAME,'99999999999999999999');
FNAME/A12 = EDIT(FIRST_NAME,'999999999999') ;
COUNTER/I5 = 1;
END


TABLE FILE DIMCLIENTPATHX
PRINT LNAME FNAME COUNTER CLIENTID_IDX SOCSECNUM SEX BIRTH_DATE
WHERE YEARS GT 17
WHERE SOCSECNUM NE '00000000' AND SOCSECNUM NE '.' AND SOCSECNUM NE ' '
ON TABLE HOLD AS HOLDCBH1
END


TABLE FILE DIMCLIENTPATHX
PRINT LNAME FNAME COUNTER CLIENTID_IDX SEX BIRTH_DATE
BY SOCSECNUM
ON TABLE HOLD AS HOLDCBH2
END


TABLE FILE SWORD
PRINT *
ON TABLE HOLD AS SWORDPATHX
END


DEFINE FILE SWORDPATHX
TESTER/A1 = EDIT(LAST_NAME,'9') ;
CLIENTID_IDX/I11 = 0;
LNAME/A20 = EDIT(LAST_NAME,'99999999999999999999');
FNAME/A12 = EDIT(FIRST_NAME,'999999999999') ;
COUNTER/I5 = 0;
END



TABLE FILE SWORDPATHX
PRINT LAST_NAME FIRST_NAME BIRTH_DATE SEX RACE ETHNICITY ALAST_NAME AFIRST_NAME AREA_CODE PHONE MESSAGE_PL TRANSEXUAL SEXUAL_MINOR HEALTH_INSUR ENGLISH_SP MILITARY EMERG_LAST
EMERG_FIRST EMERG_NATURE EMERG_ADDR EMERG_CITY EMERG_AREA EMERG_PHONE VERIFIED DUMMY2 CLIENTID_IDX LNAME FNAME COUNTER

WHERE SOCSECNUM NE ' ' OR
SOCSECNUM EQ ' ' AND LAST_NAME NE ' ' AND FIRST_NAME NE ' ' OR
SOCSECNUM EQ ' ' AND LAST_NAME NE ' ' AND BIRTH_DATE NE ' ' OR
SOCSECNUM EQ ' ' AND FIRST_NAME NE ' ' AND BIRTH_DATE NE ' '
WHERE SOCSECNUM NOT CONTAINS 'A' AND
SOCSECNUM NOT CONTAINS 'E' AND
SOCSECNUM NOT CONTAINS 'I' AND
SOCSECNUM NOT CONTAINS 'O' AND
SOCSECNUM NOT CONTAINS 'U' AND
SOCSECNUM NOT CONTAINS 'a' AND
SOCSECNUM NOT CONTAINS 'e' AND
SOCSECNUM NOT CONTAINS 'i' AND
SOCSECNUM NOT CONTAINS 'o' AND
SOCSECNUM NOT CONTAINS 'u' AND
SOCSECNUM NOT CONTAINS 'x' AND
SOCSECNUM NOT CONTAINS ' ' AND
SOCSECNUM NE '000000000' AND
SOCSECNUM NE '999999999' AND
SOCSECNUM NE '123456789' AND
SOCSECNUM NE '987654321'

WHERE (CONTACT_DATE GE 08012006 AND CONTACT_DATE LE 12012006)
BY SOCSECNUM
ON TABLE HOLD AS HOLDSWORD1
END


TABLE FILE HOLDSWORD1
PRINT LNAME FNAME BIRTH_DATE SEX CLIENTID_IDX LAST_NAME FIRST_NAME COUNTER
BY SOCSECNUM
ON TABLE HOLD AS HOLDSWORD2
END



MODIFY FILE HOLDSWORD2

FIXFORM FROM HOLDCBH2

MATCH SOCSECNUM

ON MATCH UPDATE COUNTER
ON NOMATCH REJECT

DATA ON HOLDCBH2
END


-EXIT


I limited it to 5 because using all the records causes this:

Reporting Server messages exceeded IBIF_max_messages, report retrieval aborted.

Please, contact your WebFOCUS administrator.

quote:

Originally posted by dendem:
. . .
TABLE FILE HOLDSWORD1
PRINT LNAME FNAME BIRTH_DATE SEX CLIENTID_IDX LAST_NAME FIRST_NAME COUNTER
BY SOCSECNUM
ON TABLE HOLD AS HOLDSWORD2
END



MODIFY FILE HOLDSWORD2
FIXFORM FROM HOLDCBH2
MATCH SOCSECNUM
ON MATCH UPDATE COUNTER
ON NOMATCH REJECT
DATA ON HOLDCBH2
END

If you are not holding HOLDSWORD2 as a Focus file, how do you expect MODIFY to update it?


Beyond that, FOC415 is simply a message generated (by default) when a nomatch condition is triggered. The data records displayed after each message indicate that all five transaction records that you pulled have ######### as the value of the value of SOCSECNUM/A9, and whatever Focus file you are actually addressing in your MODIFY has no such value (which is probably a good thing).

You're reaping exactly what you sowed.

This message has been edited. Last edited by: j.gross,

I changed the SWORD holds to be FORMAT FOCUS but now it is saying:

0 ERROR AT OR NEAR LINE 117 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC439) WARNING. A MATCH CONDITION HAS BEEN ASSUMED FOR: FOCLIST


What is FOCLIST?

FOCLIST: Look at the master file generated for the Focus hold file. Whenever you use PRINT ... FORMAT FOCUS, FOCLIST is added to your BY fields, to allow for duplicated.


You need to think though the heuristic reconciliation process you are trying to perform, and design your data and process to take into account that your 'dirty' data can include multiple instances with the identical ssn value.

I would assign a unique id number to the dirty records, and hold them as a Focus file. treat that as the sole key field for purposes of updating the dirty record instances with status information. Include a field to hold the to-be-determined ssn, and other status fields as needed.

Use 'match file' to look for candidate ssn: count the number of matches in the good data, based on other attributes (say last name and zip code), and match against the as-yet-unmatched record in the dirty data. where the number of matching good records is exactly one, you've got a candidate ssn value to assign to any matching 'dirty' records; create transaction records from the HOLD file produced by the Match File, and update your compendium of dirty records accordingly.

Then try another MATCH FILE criterion and repeat.

At some point you run out of unassigned records, or productive matching ideas, or time/budget, and leave the rest to manual research.

This now looks to be resolved. I needed to attack the issue a different way but it works now. Thank you.