Summarize entries based on multiple fields
Moderators: chulett, rschirm, roy
If the sum of the credit amount is not higher than the sum of debit amount
Then that means
Sum of debit amount is higher than the sum of credit amount so then subtract sum of the credit amount from the sum of the debit amount and set the DBCR-CD to D.
If there is only one type of transaction for an account then that transaction simply gets summed up.
for ex:
Account A has C of $10, Account A also has C of $20. Then summarized as
DBR-CD as C and AMT as $30 (10 + 20)
Then that means
Sum of debit amount is higher than the sum of credit amount so then subtract sum of the credit amount from the sum of the debit amount and set the DBCR-CD to D.
If there is only one type of transaction for an account then that transaction simply gets summed up.
for ex:
Account A has C of $10, Account A also has C of $20. Then summarized as
DBR-CD as C and AMT as $30 (10 + 20)
OK, so what you tried is in the first post? Group by the account number and aggregate the two values, putting the 'C' values in one field and the 'D' values in the other. If you don't have any for a particular type that sum will be zero or will need to be made zero. I don't see a need to carry the 'C' or 'D' forward as all you need are the two sums. Once you have them just compare them. Off the top of my head:
AMT:
If SUM_C > SUM_D then (SUM_C - SUM_D) else (SUM_D - SUM_C)
CODE:
If SUM_C > SUM_D then 'C' else 'D'
You'll have to figure out what is appropriate to do when they net to zero.
AMT:
If SUM_C > SUM_D then (SUM_C - SUM_D) else (SUM_D - SUM_C)
CODE:
If SUM_C > SUM_D then 'C' else 'D'
You'll have to figure out what is appropriate to do when they net to zero.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
The problem is the DBR-CD either 'C' or 'D' is coming in a different column and the AMT assoicated with that DBR-CD is coming in a different column.
Also in the input stream there are other columns coming in that needs to be mapped to output but should not be sent to agregator since I dont want to group by those columns
Also in the input stream there are other columns coming in that needs to be mapped to output but should not be sent to agregator since I dont want to group by those columns
I don't see that as a problem. Can you tell me why you think it is? Curious what I might be missing.
As to your second sentence, show us what you are actually doing. Full disclosure. There was no mention of other fields in your posted example, which was why I answered you as I did.
As to your second sentence, show us what you are actually doing. Full disclosure. There was no mention of other fields in your posted example, which was why I answered you as I did.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Try to implement this:
> Aggregate using ACCT and DBR-CD. So, you will get max two rows for each account and all the corresponding amount summed
> then combine both the rows into single - now if any accnt has both C and D, you will have both the columns in the same row
> delete the duplicates - so that you will have only one row per account (if it has both D and C, make sure to keep the row with both the columns)
> using a transformer, implement the logic (if D is more or C is more, etc) if only one is filled, just move it output
> Aggregate using ACCT and DBR-CD. So, you will get max two rows for each account and all the corresponding amount summed
> then combine both the rows into single - now if any accnt has both C and D, you will have both the columns in the same row
> delete the duplicates - so that you will have only one row per account (if it has both D and C, make sure to keep the row with both the columns)
> using a transformer, implement the logic (if D is more or C is more, etc) if only one is filled, just move it output