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,
September 10, 2009, 11:10 AM
Tony A
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
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
September 10, 2009, 11:13 AM
GamP
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.
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
September 10, 2009, 12:53 PM
dendem
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
September 10, 2009, 12:54 PM
dendem
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
September 10, 2009, 12:57 PM
Francis Mariani
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...
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
September 10, 2009, 01:05 PM
dendem
But I only want to match on one of the fields. I don't want the other 2 influencing the match.
September 11, 2009, 09:07 AM
PBrightwell
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?
Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes
September 11, 2009, 09:31 AM
dendem
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
For starters I want to find matches in the database on and two key fields, FIRSTNAME, LASTNAME, SSN or DOB
September 11, 2009, 09:48 AM
dendem
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
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
September 11, 2009, 10:16 AM
TexasStingray
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
Scott
September 11, 2009, 10:25 AM
GamP
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.
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
September 11, 2009, 10:34 AM
dendem
I am trying the match file command now. I will update once resolved.
September 16, 2009, 12:54 PM
dendem
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
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
September 16, 2009, 01:40 PM
dendem
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.
September 16, 2009, 02:01 PM
j.gross
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,
- Jack Gross WF through 8.1.05
September 16, 2009, 02:37 PM
dendem
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?
September 16, 2009, 02:58 PM
j.gross
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.
- Jack Gross WF through 8.1.05
September 18, 2009, 03:00 PM
dendem
This now looks to be resolved. I needed to attack the issue a different way but it works now. Thank you.