Hi,
I have problem in summing up the values. can anyone help me out.
I have the database records which look like this.
CUSP_N : 033234567
Account#1
SRC_SYS_C TOT_SHQ_C MONTH1 MONTH2 FXDINC 1000 500 500
MM 1000 500 500
CORPACTFUL 1000 500 500
PBACK 1000 500 0
CUSP_N : 033234567
Account#2
SRC_SYS_C TOT_SHQ_C MONTH1 MONTH2
CORPACTFUL 500 500 500
PPBACK 1000 100 0



Problem Specification.
1. Sum all the TOT_SHQ_C v
alues of two accounts.
EXCLUDING PPBACK Records
2. Sum all the MONTH1
and MONTH2 Values. Including PPBACK Records.
Logic Implemented.


1. Redefine the TOT_SHQ_C column. and replace all the PPBACK's TOT_SHQ_C as MISSING.


2. SUM all TOT_SHQ_C and MONTH1,MONTH2
values group by CUSP_N



Final Output:

CUSP_N Quantity Month1 Month2

033234567 3500 2600 2000
****************************************************

PRINT TOT_SHR_Q
MONTH1 MONTH2

WHERE V_BRANCH_USER_ACCESS.FBSI_BRCH_C EQ V_ACCT_PROJECTED_CASH.FBSI_BRCH_C

WHERE V_BRANCH_USER_ACCESS.FBSI_FIRM_C EQ V_ACCT_PROJECTED_CASH.FBSI_FIRM_C


ON TABLE HOLD AS UNION1


ON TABLE SET HOLDLIST PRINTONLY

END

-RUN



DEFINE FILE UNION1

TOT_SHR_Q/D19.2 MISSING ON NEEDS SOME = IF SRC_SYS_C EQ 'PPBACK'
THEN MISSING ELSE TOT_SHR_Q;

END



TABLE FILE UNION1


SUM
TOT_SHR_Q

MONTH1

MONTH2

BY

CUSP_N


ON TABLE HOLD AS HOLD_TEMP

ON TABLE SET ASNAMES ON
END

-RUN
**********************************************************

Current Modification needs to be done
-------------------------------------


1. Select one record from each account( It can be either FXDINC, MM, CORPACTFUL from Account#1) and calculate the TOT_SHR_Q by summing up those 2 records. Excluding the PPBACK Record.
2. Sum all the MONTH1 and MONTH2 Values. Including PPBACK Records.


Final Output Should be something like this
Final Output:


CUSP_N Quantity Month1 Month2
033234567 1500 2600 2000


Can anyone help me out how to modify this existing code. To incorporate this functionality.

Or
Any other way to implement this.

This message has been edited. Last edited by: <Mabel>,

You only need to make an adjustment to the DEFINE you've set up for the TOT_SHR_Q. This could do it for you:

TOT_SHR_Q/D19.2 MISSING ON NEEDS SOME = IF (SRC_SYS_C EQ '&SELVALUE') THEN TOT_SHR_Q ELSE MISSING;

In essence what you're saying is that if the SRC_SYS_C is PPBACK or any value other than that selected (contained in &SELVALUE) then you don't want to include that amount. ONLY if the SRC_SYS_C is equal to the selected value, do you want to sum it's amount). The PPBACK value will be screened out by the ELSE MISSING portion of the IF.

BTW...you don't need to put this in a DEFINE. It can be a COMPUTEd field.

Ken

Try this, if it doesn't matter WHICH of the records you choose, so long as it isn't PAYBACK.
DEFINE FILE UNION1
CTR/I1 WITH SRC_SYS_C = IF ACCOUNT EQ LAST ACCOUNT AND SRC_SYS_C EQ 'PPBACK' THEN CTR ELSE
IF ACCOUNT EQ LAST ACCOUNT THEN CTR + 1 ELSE
IF SRC_SYS_C EQ 'PPBACK' THEN 0 ELSE 1;
QTY/D19.2 = IF CTR EQ 1 THEN TOT_SHR_Q ELSE 0;
END
TABLE FILE UNION1
SUM
QTY TOT_SHR_Q
MONTH1
MONTH2
BY
CUSP_N
END