Would appreciate it if anyone could assist as to how code it.
Thanks...JS
Below is the code snippet :
JOIN INNER FILE APPL_V3 AT APPL_V3.ANKIPN TO MULTIPLE FILE CUST_V3 AT CUST_V3.IPKIPN TAG J0 AS J0 END
DEFINE FILE APPL_V3 DATE1/A10='&dateFrom'; DD1/A2=EDIT(DATE1,'99$$$$$$$$'); MM1/A2=EDIT(DATE1,'$$$99$$$$$'); YY1/A4=EDIT(DATE1,'$$$$$$9999'); DATE2/A10='&dateTo'; DD2/A2=EDIT(DATE2,'99$$$$$$$$'); MM2/A2=EDIT(DATE2,'$$$99$$$$$'); YY2/A4=EDIT(DATE2,'$$$$$$9999'); DATEFROM/YYMD=DATECVT(EDIT(DD1|MM1|YY1),'I8DMYY','I8YYMD'); DATETO/YYMD=DATECVT(EDIT(DD2|MM2|YY2),'I8DMYY','I8YYMD'); CURRDT/DMYY='&DMYY'; STDT/DMYY=HDATE(APPL_V3.ANSTDT, 'YYMD'); APPRCNT/I7=IF ANAVST EQ '9' THEN 1 ELSE 0; DECLCNT/I7=IF ANAVST EQ '2' THEN 1 ELSE 0; END TABLE FILE APPL_V3 SUM APPRCNT AS 'APPROVED' DECLCNT AS 'DECLINED' BY ANAPRF AS 'BRANCH' WHERE ANOTYP EQ 'IASLAH'; WHERE STDT GE DATEFROM; WHERE STDT LE DATETO; ON TABLE COLUMN-TOTAL AS 'TOTAL' END
DEFINE FILE APPL_V3 DATE1/A10='&dateFrom'; DD1/A2=EDIT(DATE1,'99$$$$$$$$'); MM1/A2=EDIT(DATE1,'$$$99$$$$$'); YY1/A4=EDIT(DATE1,'$$$$$$9999'); DATE2/A10='&dateTo'; DD2/A2=EDIT(DATE2,'99$$$$$$$$'); MM2/A2=EDIT(DATE2,'$$$99$$$$$'); YY2/A4=EDIT(DATE2,'$$$$$$9999'); DATEFROM/YYMD=DATECVT(EDIT(DD1|MM1|YY1),'I8DMYY','I8YYMD'); DATETO/YYMD=DATECVT(EDIT(DD2|MM2|YY2),'I8DMYY','I8YYMD'); CURRDT/DMYY='&DMYY'; STDT1/DMYY=HDATE(APPL_V3.ANBDAT, 'YYMD'); END TABLE FILE APPL_V3 SUM CNT.STDT1 AS 'RECEIVED' BY ANAPRF NOPRINT AS 'BRANCH' NOPRINT WHERE ANOTYP EQ 'IASLAH'; WHERE STDT1 GE DATEFROM; WHERE STDT1 LE DATETO; ON TABLE COLUMN-TOTAL AS 'TOTAL' END
DEFINE FILE APPL_V3 DATE1/A10='&dateFrom'; DD1/A2=EDIT(DATE1,'99$$$$$$$$'); MM1/A2=EDIT(DATE1,'$$$99$$$$$'); YY1/A4=EDIT(DATE1,'$$$$$$9999'); DATE2/A10='&dateTo'; DD2/A2=EDIT(DATE2,'99$$$$$$$$'); MM2/A2=EDIT(DATE2,'$$$99$$$$$'); YY2/A4=EDIT(DATE2,'$$$$$$9999'); DATEFROM/YYMD=DATECVT(EDIT(DD1|MM1|YY1),'I8DMYY','I8YYMD'); DATETO/YYMD=DATECVT(EDIT(DD2|MM2|YY2),'I8DMYY','I8YYMD'); CURRDT/DMYY='&DMYY'; STDT1/DMYY=HDATE(APPL_V3.ANBDAT, 'YYMD'); END TABLE FILE APPL_V3 SUM CNT.ANAVST AS 'PENDING' BY ANAPRF AS 'BRANCH' NOPRINT WHERE APPL_V3.ANAVST EQ '3'; WHERE ANOTYP EQ 'IASLAH'; WHERE STDT1 GE DATEFROM; WHERE STDT1 LE DATETO; ON TABLE COLUMN-TOTAL AS 'TOTAL' ENDThis message has been edited. Last edited by: JS,
July 24, 2012, 11:39 AM
Venkat-
Hi JS,
Slightly modify your code and use multiple holds (approved, declined in one hold, received in another hold, pending in another hold) and then use match file "OR" to combine all of them.
This will produce the result that you're looking for!
Regards, Venkat
product release:8203 o/s: windows 10 expected o/p formats: HTML,EXCEL,PDF
July 24, 2012, 01:49 PM
Tony A
If you analyse your code and what it is actually doing, you would notice that you can replace your define with just the one before launching into a MATCH process as suggested by Venkat -
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
JOIN CLEAR *
JOIN INNER FILE APPL_V3 AT APPL_V3.ANKIPN TO MULTIPLE FILE CUST_V3 AT CUST_V3.IPKIPN TAG J0 AS J0
DEFINE FILE APPL_V3
DATE1/A10 = '&dateFrom';
DD1/A2 = EDIT(DATE1,'99$$$$$$$$');
MM1/A2 = EDIT(DATE1,'$$$99$$$$$');
YY1/A4 = EDIT(DATE1,'$$$$$$9999');
DATE2/A10 = '&dateTo';
DD2/A2 = EDIT(DATE2,'99$$$$$$$$');
MM2/A2 = EDIT(DATE2,'$$$99$$$$$');
YY2/A4 = EDIT(DATE2,'$$$$$$9999');
DATEFROM/YYMD = DATECVT(EDIT(DD1|MM1|YY1),'I8DMYY','I8YYMD');
DATETO/YYMD = DATECVT(EDIT(DD2|MM2|YY2),'I8DMYY','I8YYMD');
CURRDT/DMYY = '&DMYY';
STDT/DMYY = HDATE(APPL_V3.ANSTDT, 'YYMD');
STDT1/DMYY = HDATE(APPL_V3.ANBDAT, 'YYMD');
APPRCNT/I7 = IF ANAVST EQ '9' THEN 1 ELSE 0;
DECLCNT/I7 = IF ANAVST EQ '2' THEN 1 ELSE 0;
END
MATCH FILE APPL_V3
SUM APPRCNT AS 'APPROVED'
DECLCNT AS 'DECLINED'
BY ANAPRF AS 'BRANCH'
WHERE ANOTYP EQ 'IASLAH';
WHERE STDT GE DATEFROM;
WHERE STDT LE DATETO;
RUN
FILE APPL_V3
SUM CNT.STDT1 AS 'RECEIVED'
BY ANAPRF AS 'BRANCH'
WHERE ANOTYP EQ 'IASLAH';
WHERE STDT1 GE DATEFROM;
WHERE STDT1 LE DATETO;
AFTER MATCH HOLD AS JSHOLD1 OLD-OR-NEW
RUN
FILE APPL_V3
SUM CNT.ANAVST AS 'PENDING'
BY ANAPRF AS 'BRANCH'
WHERE APPL_V3.ANAVST EQ '3';
WHERE ANOTYP EQ 'IASLAH';
WHERE STDT1 GE DATEFROM;
WHERE STDT1 LE DATETO;
AFTER MATCH HOLD AS JSHOLD1 OLD-OR-NEW
END
-RUN
TABLE FILE JSHOLD1
PRINT *
END
-RUN
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
July 24, 2012, 01:55 PM
Tony A
Just a point, but you will notice that I have left your main code untouched. You will need to sort that out as well to get a result!
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
July 25, 2012, 07:06 AM
JS
Dear Tony & Venkat,
Thank you very much for your advise and sharing the new code.
Will try both options.
Thanks...JS
July 25, 2012, 08:34 AM
Tony A
Going from your other post(s) I would urge you to ensure that you know and understand what code you are putting in place before committing.
If you do not understand what the code does or needs to do then you are only introducing maintenance concerns which will likely "jump up and bite you" when you least suspect.
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
July 25, 2012, 10:22 PM
JS
Dear Tony,
Thank you for raising your concern.
Honestly, I struggling with the codes as I'm a non technical person. I volunteered just to get things moving.
All the issues or posts raised are in development stages. I will not deploy until I'm satisfy with the final outcome of the results.
At times, I will browse through the forum to find out if there are existing solutions to my issues and adopt whichever is applicable.