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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] new not old match not producing all records

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] new not old match not producing all records
 Login/Join
 
Member
posted
I have a fairly large hold file that I'm trying to compare to another large hold file with a new-not-old match. When I check records by employee sometimes the match works, but sometimes the hold file after the match does not have all the records that are in the original hold_paycodes table. Is my match syntax off? Here is my code:

-PROMPT &XDT1.Enter a start date (yyyy-mm-dd format);
-PROMPT &XDT2.Enter an end date (yyyy-mm-dd format);
-SET &BEGDATE = STRREP(10,&XDT1,1,'-',0,'',8,A8);
-SET &ENDDATE = STRREP(10,&XDT2,1,'-',0,'',8,A8);
-SET &BEGDATE = DATEADD(DATECVT(&BEGDATE, 'I8YYMD','YYMD'),'D',1);
-SET &SENDDATE = DATECVT(&ENDDATE, 'I8YYMD','YYMD');
-SET &ENDDATE = DATEADD(DATECVT(&ENDDATE, 'I8YYMD','YYMD'),'D',-1);
-SET &SENDDATE = DATECVT(&SENDDATE, 'YYMD','I8YYMD');
-SET &NICEBEGDATE = DATECVT(&BEGDATE, 'YYMD','A8YYMD');
-SET &NICEENDDATE = DATECVT(&ENDDATE, 'YYMD','A8YYMD');


JOIN
EMPID WITH PERSONNUM
IN WFC_VP_TIMESHTPUNCHV42
TO UNIQUE EMP_PAEMP.EMP_PAEMP.EMPLOYEE
IN EMP_PAEMP TAG J001
AS J001
END
DEFINE FILE WFC_VP_TIMESHTPUNCHV42
HOURS/D12.2=WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.TIMEINSECONDS/3600;
EMPID/P10=EDIT(WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.PERSONNUM);
SM_SEGDATE/YYMD = HDATE(WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.EVENTDATE,'YYMD');
SM_WEEKDAY/I2 = HPART(WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.EVENTDATE,'WEEKDAY','I2');
SM_PERIOD_START_DATE/YYMD = IF SM_WEEKDAY EQ 7 THEN DATEADD(SM_SEGDATE, 'D', -6) ELSE IF SM_WEEKDAY EQ 6 THEN DATEADD(SM_SEGDATE,'D',-5) ELSE IF SM_WEEKDAY EQ 5 THEN DATEADD(SM_SEGDATE,'D',-4) ELSE IF SM_WEEKDAY EQ 4 THEN DATEADD(SM_SEGDATE,'D',-3) ELSE IF SM_WEEKDAY EQ 3 THEN DATEADD(SM_SEGDATE,'D',-2) ELSE IF SM_WEEKDAY EQ 2 THEN DATEADD(SM_SEGDATE, 'D',-1) ELSE SM_SEGDATE;
SM_PERIOD_END_DATE/YYMD = IF SM_WEEKDAY EQ 1 THEN DATEADD(SM_SEGDATE,'D',6) ELSE IF SM_WEEKDAY EQ 2 THEN DATEADD(SM_SEGDATE,'D',5) ELSE IF SM_WEEKDAY EQ 3 THEN DATEADD(SM_SEGDATE,'D',4) ELSE IF SM_WEEKDAY EQ 4 THEN DATEADD(SM_SEGDATE, 'D', 3) ELSE IF SM_WEEKDAY EQ 5 THEN DATEADD(SM_SEGDATE, 'D',2) ELSE IF SM_WEEKDAY EQ 6 THEN DATEADD(SM_SEGDATE, 'D', 1) ELSE SM_SEGDATE;
FORMATTED_TERM_DATE/YYMD = IF J001.EMP_PAEMP.TERM_DATE NE '1700-01-01' THEN J001.EMP_PAEMP.TERM_DATE ELSE '';
UNIQUEID/A20= WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.PERSONNUM || DATECVT(SM_SEGDATE,'YYMD','A8YYMD');
END
TABLE FILE WFC_VP_TIMESHTPUNCHV42
-*ON TABLE HOLD AS HOLD_PUNCHES FORMAT FOCUS INDEX WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.EMPID
PRINT
-* FORMATTED_OUTPUNCH
WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.PAYCODENAME
WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.HOURS
WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.LABORLEVELNAME5 AS 'JOB_CODE'
WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.LABORLEVELNAME7 AS 'DEPT'
WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.EMPID NOPRINT
J001.EMP_PAEMP.DATE_HIRED
FORMATTED_TERM_DATE AS 'TERM_DATE'
SM_SEGDATE
SM_WEEKDAY
SM_PERIOD_START_DATE AS 'PERIOD_START_DATE'
SM_PERIOD_END_DATE AS 'PERIOD_END_DATE'
EMPID
SUPERVISOR
-* FORMATTED_INPUNCH_DATE
WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.LABORLEVELNAME2 AS 'COST_CENTER'
BY WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.PERSONNUM
BY WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.UNIQUEID
BY WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.EVENTDATE
BY WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.PERSONFULLNAME
BY WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.INPUNCHDTM
BY WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.OUTPUNCHDTM
-*WHERE WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.PERSONNUM EQ '1083012'
-*WHERE WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.PERSONNUM EQ '628026'
WHERE WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.PERSONNUM EQ '1586093'
-*HEADING
-*"Kronos punches for &NICEBEGDATE to &NICEENDDATE"
-*" "
WHERE WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.LABORLEVELNAME2 EQ '96100';
WHERE WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.EVENTDATE GT DT(&XDT1);
WHERE WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.EVENTDATE LT DT(&XDT2);
WHERE EMPID NE 0;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT XLSX
ON TABLE HOLD AS HOLD_PUNCHES FORMAT FOCUS INDEX WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.EMPID WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.UNIQUEID
END

-*************** get vp_totals
JOIN
EMPID WITH PERSONNUM
IN WFC_VP_TOTALS
TO UNIQUE EMP_PAEMP.EMP_PAEMP.EMPLOYEE
IN EMP_PAEMP TAG J002
AS J002
END
DEFINE FILE WFC_VP_TOTALS
HOURS/D12.2=WFC_VP_TOTALS.WFC_VP_TOTALS.TIMEINSECONDS/3600;
EMPID/P10=EDIT(WFC_VP_TOTALS.WFC_VP_TOTALS.PERSONNUM);
SM_SEGDATE/YYMD = WFC_VP_TOTALS.WFC_VP_TOTALS.APPLYDATE;
SM_WEEKDAY/W = WFC_VP_TOTALS.WFC_VP_TOTALS.APPLYDATE;
SM_PERIOD_START_DATE/YYMD = IF SM_WEEKDAY EQ 7 THEN DATEADD(SM_SEGDATE, 'D', -6) ELSE IF SM_WEEKDAY EQ 6 THEN DATEADD(SM_SEGDATE,'D',-5) ELSE IF SM_WEEKDAY EQ 5 THEN DATEADD(SM_SEGDATE,'D',-4) ELSE IF SM_WEEKDAY EQ 4 THEN DATEADD(SM_SEGDATE,'D',-3) ELSE IF SM_WEEKDAY EQ 3 THEN DATEADD(SM_SEGDATE,'D',-2) ELSE IF SM_WEEKDAY EQ 2 THEN DATEADD(SM_SEGDATE, 'D',-1) ELSE SM_SEGDATE;
SM_PERIOD_END_DATE/YYMD = IF SM_WEEKDAY EQ 1 THEN DATEADD(SM_SEGDATE,'D',6) ELSE IF SM_WEEKDAY EQ 2 THEN DATEADD(SM_SEGDATE,'D',5) ELSE IF SM_WEEKDAY EQ 3 THEN DATEADD(SM_SEGDATE,'D',4) ELSE IF SM_WEEKDAY EQ 4 THEN DATEADD(SM_SEGDATE, 'D', 3) ELSE IF SM_WEEKDAY EQ 5 THEN DATEADD(SM_SEGDATE, 'D',2) ELSE IF SM_WEEKDAY EQ 6 THEN DATEADD(SM_SEGDATE, 'D', 1) ELSE SM_SEGDATE;
FORMATTED_TERM_DATE/YYMD = IF J002.EMP_PAEMP.TERM_DATE NE '1700-01-01' THEN J002.EMP_PAEMP.TERM_DATE ELSE '';
UNIQUEID/A20= WFC_VP_TOTALS.WFC_VP_TOTALS.PERSONNUM || DATECVT(SM_SEGDATE,'YYMD','A8YYMD');
INPUNCHDTM/HYYMDS = '';
OUTPUNCHDTM/HYYMDS = '';
END
TABLE FILE WFC_VP_TOTALS
-*ON TABLE HOLD AS HOLD_PUNCHES FORMAT FOCUS INDEX WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.EMPID
PRINT
-* FORMATTED_OUTPUNCH
WFC_VP_TOTALS.WFC_VP_TOTALS.PAYCODENAME
WFC_VP_TOTALS.WFC_VP_TOTALS.HOURS
WFC_VP_TOTALS.WFC_VP_TOTALS.LABORLEVELNAME5 AS 'JOB_CODE'
WFC_VP_TOTALS.WFC_VP_TOTALS.LABORLEVELNAME7 AS 'DEPT'
WFC_VP_TOTALS.WFC_VP_TOTALS.EMPID NOPRINT
J002.EMP_PAEMP.DATE_HIRED
FORMATTED_TERM_DATE AS 'TERM_DATE'
SM_SEGDATE
SM_WEEKDAY
SM_PERIOD_START_DATE AS 'PERIOD_START_DATE'
SM_PERIOD_END_DATE AS 'PERIOD_END_DATE'
EMPID
SUPERVISOR
-* FORMATTED_INPUNCH_DATE
WFC_VP_TOTALS.WFC_VP_TOTALS.LABORLEVELNAME2 AS 'COST_CENTER'
BY WFC_VP_TOTALS.WFC_VP_TOTALS.PERSONNUM
BY WFC_VP_TOTALS.WFC_VP_TOTALS.UNIQUEID
BY WFC_VP_TOTALS.WFC_VP_TOTALS.APPLYDATE AS 'EVENTDATE'
BY WFC_VP_TOTALS.WFC_VP_TOTALS.PERSONFULLNAME
BY INPUNCHDTM
BY OUTPUNCHDTM
-*WHERE WFC_VP_TOTALS.WFC_VP_TOTALS.PERSONNUM EQ '1083012'
-*WHERE WFC_VP_TOTALS.WFC_VP_TOTALS.PERSONNUM EQ '628026'
WHERE WFC_VP_TOTALS.WFC_VP_TOTALS.PERSONNUM EQ '1586093'
WHERE WFC_VP_TOTALS.WFC_VP_TOTALS.PAYCODENAME NE 'REGULAR' OR 'OVERTIME';
-*HEADING
-*"Kronos punches for &NICEBEGDATE to &NICEENDDATE"
-*" "
WHERE WFC_VP_TOTALS.WFC_VP_TOTALS.LABORLEVELNAME2 EQ '96100';
WHERE WFC_VP_TOTALS.WFC_VP_TOTALS.APPLYDATE GT DT(&XDT1);
WHERE WFC_VP_TOTALS.WFC_VP_TOTALS.APPLYDATE LT DT(&XDT2);
WHERE EMPID NE 0;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT XLSX
ON TABLE HOLD AS HOLD_PAYCODES FORMAT FOCUS INDEX WFC_VP_TOTALS.WFC_VP_TOTALS.EMPID WFC_VP_TOTALS.WFC_VP_TOTALS.UNIQUEID
END

MATCH FILE HOLD_PUNCHES
BY HOLD_PUNCHES.SEG01.UNIQUEID
RUN
FILE HOLD_PAYCODES
PRINT
HOLD_PAYCODES.SEG01.EVENTDATE
HOLD_PAYCODES.SEG01.PERSONFULLNAME
HOLD_PAYCODES.SEG01.INPUNCHDTM
HOLD_PAYCODES.SEG01.OUTPUNCHDTM
HOLD_PAYCODES.SEG01.PAYCODENAME
HOLD_PAYCODES.SEG01.HOURS
HOLD_PAYCODES.SEG01.JOB_CODE
HOLD_PAYCODES.SEG01.DEPT
HOLD_PAYCODES.SEG01.DATE_HIRED
HOLD_PAYCODES.SEG01.TERM_DATE
HOLD_PAYCODES.SEG01.SM_SEGDATE
HOLD_PAYCODES.SEG01.SM_WEEKDAY
HOLD_PAYCODES.SEG01.PERIOD_START_DATE
HOLD_PAYCODES.SEG01.PERIOD_END_DATE
HOLD_PAYCODES.SEG01.EMPID
HOLD_PAYCODES.SEG01.SUPERVISOR
HOLD_PAYCODES.SEG01.COST_CENTER
HOLD_PAYCODES.SEG01.PERSONNUM
BY HOLD_PAYCODES.SEG01.UNIQUEID
WHERE HOLD_PAYCODES.SEG01.PAYCODENAME NE ''
AFTER MATCH HOLD AS HOLD_PCDS NEW-NOT-OLD
END

TABLE FILE HOLD_PCDS
PRINT *
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
-*ON TABLE HOLD AS HOLD_PCDS2 FORMAT FOCUS
END

This message has been edited. Last edited by: FP Mod Chuck,


WebFocus 8.0.0.7
Window XP SP3
Excel, PDF, HTML
 
Posts: 13 | Registered: November 29, 2011Report This Post
Member
posted Hide Post
Nevermind - I had my where syntax in the match statement wrong!


WebFocus 8.0.0.7
Window XP SP3
Excel, PDF, HTML
 
Posts: 13 | Registered: November 29, 2011Report This Post
Virtuoso
posted Hide Post
As first try I would make the UNIQUEID as the first BY field of each hold file. I pretty sure that it will solved your problem.
Also, have your filter at the HOLD step (except if you also need those data within the hold file)
 WHERE HOLD_PAYCODES.SEG01.PAYCODENAME NE ''

That way you'll have less data trying to MATCH

If not, then revert the MATCH
RUN
MATCH FILE HOLD_PAYCODES
 PRINT
 HOLD_PAYCODES.SEG01.EVENTDATE
 HOLD_PAYCODES.SEG01.PERSONFULLNAME
 HOLD_PAYCODES.SEG01.INPUNCHDTM
 HOLD_PAYCODES.SEG01.OUTPUNCHDTM
 HOLD_PAYCODES.SEG01.PAYCODENAME
	HOLD_PAYCODES.SEG01.HOURS
	HOLD_PAYCODES.SEG01.JOB_CODE
	HOLD_PAYCODES.SEG01.DEPT
	HOLD_PAYCODES.SEG01.DATE_HIRED
	HOLD_PAYCODES.SEG01.TERM_DATE
	HOLD_PAYCODES.SEG01.SM_SEGDATE
	HOLD_PAYCODES.SEG01.SM_WEEKDAY
	HOLD_PAYCODES.SEG01.PERIOD_START_DATE
	HOLD_PAYCODES.SEG01.PERIOD_END_DATE
	HOLD_PAYCODES.SEG01.EMPID
	HOLD_PAYCODES.SEG01.SUPERVISOR
	HOLD_PAYCODES.SEG01.COST_CENTER
 HOLD_PAYCODES.SEG01.PERSONNUM
 BY HOLD_PAYCODES.SEG01.UNIQUEID
-* WHERE HOLD_PAYCODES.SEG01.PAYCODENAME NE ''
FILE HOLD_PUNCHES
 BY HOLD_PUNCHES.SEG01.UNIQUEID
AFTER MATCH HOLD AS HOLD_PCDS OLD-NOT-NEW
END


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] new not old match not producing all records

Copyright © 1996-2020 Information Builders