KEY AMOUNT1 AMOUNT2 SUBKEY
----------------------------------------
ABC 100.00 200.00 BEFORE
BCD 100.00 200.00 BEFORE
ABC 100.00 200.00 AFTER
DEF 100.00 200.00 AFTER
The amounts are displayed with a decimal point
Output
KEY AMOUNT1 AMOUNT2 SUBKEY
----------------------------------------
ABC 0.00 200.00
BCD 100.00 200.00 BEFORE
DEF 100.00 200.00 AFTER
Two records exist (Subkey = BEFORE & AFTER). The AFTER amountS are subtracted from BEFORE amountS.
Conditions to be written out to output with the computed amount -
1. If it equals out to zero, SUBKEY in output will be blank
2. If Before amounts > AFTER amounts , SUBKEY in output will be BEFORE
3. If Before amounts < AFTER amounts , SUBKEY in output will be AFTER
Could you help me on this?
If I have two records(subkey = BEFORE & AFTER) with the same KEY=ABC, then I need to do as shown below
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
It's not clear what you want to do. You say "The AFTER amountS are subtracted from BEFORE amountS". But for your two ABC records, the BEFORE and AFTER values for both fields are the same, but you show 0.00 (subtract) for the first field and 200.00 (no subtract) for the second field. Why?
Also you say "If Before amounts > AFTER amounts , SUBKEY in output will be BEFORE'. I have no idea what you mean by this. What are the "Before amounts"? What are the "AFTER amounts"?
You need to show a better example of input and expected output records with all of the possible variations, and explain clearly the "rules" for getting from input to output.
Also, give the RECFM and LRECL of the your input file, and the starting position and length of each input field.
KEY X (4)
AMOUNT1 X(6)
Filler X (1)
AMOUNT2 X(6)
Filler X(1)
INDICATOR X(6)
FILLER X(6)
Sample input file
AAA 50.00 50.00 BEFORE
AXE 50.00 50.00 BEFORE
SAM 100.00 200.00 BEFORE
RAM 100.00 200.00 BEFORE
AXE 100.00 200.00 AFTER
BBB 100.00 200.00 AFTER
SAM 100.00 200.00 AFTER
RAM 50.00 50.00 AFTER
1.
Key='AXE' has two input records. One record has indicator BEFORE and the 2nd one has indicator AFTER. AMOUNT1 of indicator AFTER is subtracted from AMOUNT1 of indicator BEFORE.Since AMOUNT1 of indicator AFTER is greater than AMOUNT1 of indicator BEFORE, Indicator AFTER and AMOUNT2 of indicator AFTER are copied to output.
2.
Key='SAM' has two input records. One record has indicator BEFORE and the 2nd one has indicator AFTER. AMOUNT1 of indicator AFTER is subtracted from AMOUNT1 of indicator BEFORE.Since AMOUNT1 of indicator AFTER is equal to AMOUNT1 of indicator BEFORE, Indicator AFTER and AMOUNT2 of indicator BEFORE are copied to output.
3.
Key='RAM' has two input records. One record has indicator BEFORE and the 2nd one has indicator AFTER. AMOUNT1 of indicator AFTER is subtracted from AMOUNT1 of indicator BEFORE.Since AMOUNT1 of indicator AFTER is less than AMOUNT1 of indicator BEFORE, Indicator BEFORE and AMOUNT2 of indicator BEFORE are copied to output.
4.
If you see, Key='AAA' has one input record with indicator BEFORE.This record is copied to output as it is.
5.
If you see, Key='BBB' has one input record with indicator AFTER.This record is copied to output as it is.
This is how my output file will look like
Output file (sorted in ascending order of KEY) AAA 50.00 50.00 BEFORE
AXE 50.00 200.00 AFTER
BBB 100.00 200.00 AFTER
RAM 50.00 200.00 BEFORE
SAM 0.00 200.00 BEFORE
Note : Amount1 Amount2 are in decimal format. I guess AMOUNT1 needs to be converted to ZD using SFF before subtraction could be applied.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
I'm still trying to make sense of this and work out coding rules.
It appears that in the cases where you have a BEFORE and AFTER pair, you want the AMOUNT1 output value to be the absolute value of (AFTER AMOUNT1- BEFORE AMOUNT1) like this:
The other rules for each BEFORE and AFTER pair appear to be:
If AFTER AMOUNT1 > BEFORE AMOUNT1, use AFTER AMOUNT2 and AFTER indicator.
If AFTER AMOUNT1 < BEFORE AMOUNT1, use BEFORE AMOUNT2 and BEFORE indicator.
Is that correct?
If AFTER AMOUNT1 = BEFORE AMOUNT1, use BEFORE AMOUNT2 and AFTER indicator. I question this one, though. To be consistent, wouldn't you want the AFTER AMOUNT2?. For example, if you had:
The purpose of indicators in the o/p is to tell one which record has the greater AMOUNT1 (the record with indicator BEFORE or the record with indicator AFTER)
So in the case where AFTER AMOUNT1=BEFORE AMOUNT1 , there is no hard and fast rule that in the output the indicator should be BEFORE.
case where AFTER AMOUNT1=BEFORE AMOUNT1 , there is no hard and fast rule that in the output the indicator should be BEFORE and amount2 should be BEFORE AMOUNT2.
AAA 50.00 50.00 BEFORE
AXE 50.00 50.00 BEFORE
SAM 100.00 200.00 BEFORE
RAM 100.00 200.00 BEFORE
QQQ 50.00 100.00 BEFORE
QQQ 50.00 200.00 AFTER
AXE 100.00 200.00 AFTER
BBB 100.00 200.00 AFTER
SAM 100.00 200.00 AFTER
RAM 50.00 50.00 AFTER
SORTOUT would have these records:
Code:
AAA 50.00 50.00 BEFORE
AXE 50.00 200.00 AFTER
BBB 100.00 200.00 AFTER
QQQ 0.00 100.00 BEFORE
RAM 50.00 200.00 BEFORE
SAM 0.00 200.00 BEFORE
