Hi all, I am trying to merge two files using the MATCH phrase. I would like to save records that appear only in the new data. I read about this and think that I must use NEW-NOT-OLD merge type.
Here is data in the old:
TABLE FILE EMPLOYEE
SUM EMPLOYEE.EMPLOYEE.RPTMONTH
BY EMPLOYEE.EMPLOYEE.DIVISION
END
TABLE FILE TOP50_DIVISION
BY TOP50_DIVISION.TOP50_DIVISION.DIV_CD
AFTER MATCH HOLD NEW-NOT-OLD
END
PAGE 1
DIV_CD
AF
ASD
Admin
CFSD
CW
DCC
DDSD
DSD
Exec
FIN
GC
I&R
OBQ
OCC
OCSS
OIG
OSS
XX
YY
Here is the complete Merge code:
MATCH FILE EMPLOYEE
SUM EMPLOYEE.EMPLOYEE.RPTMONTH
BY EMPLOYEE.EMPLOYEE.DIVISION
RUN
FILE TOP50_DIVISION
BY TOP50_DIVISION.TOP50_DIVISION.DIV_CD
AFTER MATCH HOLD NEW-NOT-OLD
END
TABLE FILE HOLD
BY 'HOLD.HOLD.DIV_CD'
ON TABLE NOTOTAL
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
$
ENDSTYLE
END
Here is the result from the merge:
DIV_CD
AF
ASD
Admin
CFSD
CW
DCC
DDSD
DSD
Exec
FIN
GC
I&R
OBQ
OCC
OCSS
OIG
OSS
XX
YY
I was hoping to get the following result instead:
DIV_CD
Admin
GC
XX
YY
I believe, I should have gotten ‘Admin’, ‘GC’,’XX’, and ‘YY’. Can anyone tell why I am not getting just these 4 division codes? These 4 are the only ones that are in the new but not the old file.
Thank you for your input,
SeyedThis message has been edited. Last edited by: SeyedG,
April 24, 2013, 03:35 PM
Dan Satchell
When using MATCH logic, the columns being matched must have the same name - otherwise you basically get a merging of the two datasets.
MATCH FILE EMPLOYEE
SUM EMPLOYEE.EMPLOYEE.RPTMONTH
BY EMPLOYEE.EMPLOYEE.DIVISION AS 'DIV_CD'
RUN
FILE TOP50_DIVISION
BY TOP50_DIVISION.TOP50_DIVISION.DIV_CD
AFTER MATCH HOLD NEW-NOT-OLD
END
Or....
MATCH FILE EMPLOYEE
SUM EMPLOYEE.EMPLOYEE.RPTMONTH
BY EMPLOYEE.EMPLOYEE.DIVISION
RUN
FILE TOP50_DIVISION
BY TOP50_DIVISION.TOP50_DIVISION.DIV_CD AS 'DIVISION'
AFTER MATCH HOLD NEW-NOT-OLD
END
WebFOCUS 7.7.05
April 24, 2013, 03:51 PM
SeyedG
Hi Dan, Thank you very much for taking time to assist me with this. Per your suggestion, I gave the division column an alias called 'DIV_CD' and that fixed the MATCH logic and I got the correct result.
Thanks again and continued success,
Seyed
WebFOCUS 8.0.09 App Studio 8009 Linux Kernel-2.6 DBMS: Oracle 11g all output (Excel, HTML, AHTML, PDF)