Page 1 of 1
Posted: Fri Oct 24, 2014 2:02 pm
by chulett
What are the rules if there is only one type of transaction? Or if the sum of the credit amount is not higher than the debit amount?
Posted: Sun Oct 26, 2014 12:23 pm
by oracledba
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)
Posted: Sun Oct 26, 2014 2:25 pm
by chulett
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.
Posted: Fri Oct 31, 2014 12:59 pm
by oracledba
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
Posted: Fri Oct 31, 2014 1:10 pm
by chulett
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.
Posted: Mon Nov 03, 2014 1:49 pm
by yugee
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