Hi, Below is my data
Clr doc_# | Clearing_Date | Invoice_Amount |
85212889 | 20-Oct-14 | -205.34 |
85212889 | 20-Oct-14 | -205.34 |
482032929 | 5-Nov-14 | 319.81 |
482032929 | 5-Nov-14 | -319.81 |
835181114 | 3-Nov-14 | 331.65 |
257495 | 18-Dec-14 | -10475.70 |
257495 | 18-Dec-14 | 10475.70 |
257495 | 18-Dec-14 | 10475.70 |
343898098 | 19-Dec-14 | 2000.00 |
343898098 | 19-Dec-14 | -1000.00 |
343898098 | 19-Dec-14 | -1000.00 |
343898098 | 19-Dec-14 | 500.00 |
2370 | 9-Dec-14 | 11132.00 |
2370 | 9-Dec-14 | 20.00 |
3308 | 8-Oct-14 | 2000.00 |
3308 | 8-Oct-14 | -1000.00 |
3308 | 8-Oct-14 | -1000.00 |
1234 | 8-Oct-14 | -2000.00 |
1234 | 8-Oct-14 | 1000.00 |
1234 | 8-Oct-14 | 1000.00 |
Expected Output:
Clr doc_# | Clearing_Date | Invoice_Amount | Manual flag |
85212889 | 20-Oct-14 | -205.34 | I |
85212889 | 20-Oct-14 | -205.34 | I |
835181114 | 3-Nov-14 | 331.65 | I |
257495 | 18-Dec-14 | 10475.70 | I |
343898098 | 19-Dec-14 | 500.00 | I |
2370 | 9-Dec-14 | 11132.00 | I |
2370 | 9-Dec-14 | 20.00 | I |
Logic is groupby doc_num and date: eliminate those records where sum of amount of n rows for each doc# is zero
for Example: for doc_num 257495: we have 3 records but if we sum up invoice amount (-10475.70)+(10475.70)+(10475.70) = 10475.70, so instead of 3 records, I should get only one record that has data like below and create a column as Manual flag that has I=Include, E= Exclude
257495 18-Dec-14 10475.70 I
Solved! Go to Solution.
Yes, I would like have two lines for doc num 85212889 because if we see the amount for that particular doc num both has -205.34, so if we add both the amounts sum is not zero.
I can give a scenario:
Bank pays to customer $100 by mistake with doc num A101 and on some date (Lets take 10/Aug/2017). Then Customer realises its by mistake, so Customer pays back to the bank $100 with same doc num A101 and on same date. So it is zero, neither a loss to bank nor to customer. So, I would like to eliminate these two records.
There could be chance where only bank pays to customer twice $100 but customer wont payback. In this case, I would like to have both the records in my dataset.
Ok, so I looked through it and determined that what you are trying to do is nett off invoices to credits, and so only nett off those where positive and negative values exist.
If there are only negative values, or only positive values, then show all rows.
I've attached the workflow which achieves this.
Let me know if this is correct or if I've misunderstood the logic.