Dear All,

I'm trying to built a report where the end result shd be :

BRANCHES APPROVED DECLINED RECEIVED PENDING
BRANCH1 xxxx xxxx xxxx xxxx
BRANCH2 YYYY YYYY YYYY YYYY

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'
END

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

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

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

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

Dear Tony & Venkat,

Thank you very much for your advise and sharing the new code.

Will try both options.


Thanks...JS

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

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.


Regards...JS